def get_stock_qty(item_code, warehouse):
return get_previous_sle({
"item_code": item_code,
"warehouse": warehouse,
"posting_date": nowdate(),
"posting_time": nowtime()
}).get("qty_after_transaction") or 0
def get_incoming_rate(args):
"""Get Incoming Rate based on valuation method"""
from erpnext.stock.stock_ledger import get_previous_sle, get_valuation_rate
if isinstance(args, basestring):
args = json.loads(args)
in_rate = 0
if (args.get("serial_no") or "").strip():
in_rate = get_avg_purchase_rate(args.get("serial_no"))
else:
valuation_method = get_valuation_method(args.get("item_code"))
previous_sle = get_previous_sle(args)
if valuation_method == 'FIFO':
if previous_sle:
previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]')
in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0
elif valuation_method == 'Moving Average':
in_rate = previous_sle.get('valuation_rate') or 0
if not in_rate:
voucher_no = args.get('voucher_no') or args.get('name')
in_rate = get_valuation_rate(args.get('item_code'), args.get('warehouse'),
args.get('voucher_type'), voucher_no, args.get('allow_zero_valuation'),
currency=erpnext.get_company_currency(args.get('company')), company=args.get('company'))
return in_rate
def update_stock_ledger(self):
""" find difference between current and expected entries
and create stock ledger entries based on the difference"""
from erpnext.stock.stock_ledger import get_previous_sle
row_template = ["item_code", "warehouse", "qty", "valuation_rate"]
if not self.reconciliation_json:
msgprint(_("""Stock Reconciliation file not uploaded"""), raise_exception=1)
data = json.loads(self.reconciliation_json)
for row_num, row in enumerate(data[data.index(self.head_row)+1:]):
row = frappe._dict(zip(row_template, row))
row["row_num"] = row_num
if row.qty in ("", None) or row.valuation_rate in ("", None):
previous_sle = get_previous_sle({
"item_code": row.item_code,
"warehouse": row.warehouse,
"posting_date": self.posting_date,
"posting_time": self.posting_time
})
if row.qty in ("", None):
row.qty = previous_sle.get("qty_after_transaction")
if row.valuation_rate in ("", None):
row.valuation_rate = previous_sle.get("valuation_rate")
# if row.qty and not row.valuation_rate:
# frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code))
self.insert_entries(row)
def test_delivery_note_no_gl_entry(self):
set_perpetual_inventory(0)
self.assertEqual(cint(frappe.defaults.get_global_default("auto_accounting_for_stock")), 0)
make_stock_entry(target="_Test Warehouse - _TC", qty=5, basic_rate=100)
stock_queue = json.loads(
get_previous_sle(
{
"item_code": "_Test Item",
"warehouse": "_Test Warehouse - _TC",
"posting_date": nowdate(),
"posting_time": nowtime(),
}
).stock_queue
or "[]"
)
dn = create_delivery_note()
sle = frappe.get_doc("Stock Ledger Entry", {"voucher_type": "Delivery Note", "voucher_no": dn.name})
self.assertEqual(sle.stock_value_difference, -1 * stock_queue[0][1])
self.assertFalse(get_gl_entries("Delivery Note", dn.name))
def set_actual_qty(self):
allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock"))
for d in self.get("items"):
previous_sle = get_previous_sle(
{
"item_code": d.item_code,
"warehouse": d.s_warehouse or d.t_warehouse,
"posting_date": self.posting_date,
"posting_time": self.posting_time,
}
)
# get actual stock at source warehouse
d.actual_qty = previous_sle.get("qty_after_transaction") or 0
# validate qty during submit
if d.docstatus == 1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
frappe.throw(
_(
"""Row {0}: Qty not avalable in warehouse {1} on {2} {3}.
Available Qty: {4}, Transfer Qty: {5}"""
).format(d.idx, d.s_warehouse, self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty),
NegativeStockError,
)
def set_actual_qty(self):
allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock"))
for d in self.get("items"):
previous_sle = get_previous_sle(
{
"item_code": d.item_code,
"warehouse": d.s_warehouse or d.t_warehouse,
"posting_date": self.posting_date,
"posting_time": self.posting_time,
}
)
# get actual stock at source warehouse
d.actual_qty = previous_sle.get("qty_after_transaction") or 0
# validate qty during submit
if d.docstatus == 1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
frappe.throw(
_(
"Row {0}: Qty not available for {4} in warehouse {1} at posting time of the entry ({2} {3})".format(
d.idx,
frappe.bold(d.s_warehouse),
formatdate(self.posting_date),
format_time(self.posting_time),
frappe.bold(d.item_code),
)
)
+ "<br><br>"
+ _("Available qty is {0}, you need {1}").format(
frappe.bold(d.actual_qty), frappe.bold(d.transfer_qty)
),
NegativeStockError,
title=_("Insufficient Stock"),
)
def update_stock_ledger(self):
""" find difference between current and expected entries
and create stock ledger entries based on the difference"""
from erpnext.stock.stock_ledger import get_previous_sle
for row in self.items:
previous_sle = get_previous_sle({
"item_code": row.item_code,
"warehouse": row.warehouse,
"posting_date": self.posting_date,
"posting_time": self.posting_time
})
if previous_sle:
if row.qty in ("", None):
row.qty = previous_sle.get("qty_after_transaction", 0)
if row.valuation_rate in ("", None):
row.valuation_rate = previous_sle.get("valuation_rate", 0)
if row.qty and not row.valuation_rate:
frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code))
if ((previous_sle and row.qty == previous_sle.get("qty_after_transaction")
and row.valuation_rate == previous_sle.get("valuation_rate"))
or (not previous_sle and not row.qty)):
continue
self.insert_entries(row)
def _test_reco_sle_gle(self, valuation_method):
set_perpetual_inventory()
# [[qty, valuation_rate, posting_date,
# posting_time, expected_stock_value, bin_qty, bin_valuation]]
input_data = [
[50, 1000, "2012-12-26", "12:00"],
[25, 900, "2012-12-26", "12:00"],
["", 1000, "2012-12-20", "12:05"],
[20, "", "2012-12-26", "12:05"],
[0, "", "2012-12-31", "12:10"]
]
for d in input_data:
repost_stock_as_per_valuation_method(valuation_method)
last_sle = get_previous_sle({
"item_code": "_Test Item",
"warehouse": "_Test Warehouse - _TC",
"posting_date": d[2],
"posting_time": d[3]
})
# submit stock reconciliation
stock_reco = create_stock_reconciliation(qty=d[0], rate=d[1],
posting_date=d[2], posting_time=d[3])
# check stock value
sle = frappe.db.sql("""select * from `tabStock Ledger Entry`
where voucher_type='Stock Reconciliation' and voucher_no=%s""", stock_reco.name, as_dict=1)
qty_after_transaction = flt(d[0]) if d[0] != "" else flt(last_sle.get("qty_after_transaction"))
valuation_rate = flt(d[1]) if d[1] != "" else flt(last_sle.get("valuation_rate"))
if qty_after_transaction == last_sle.get("qty_after_transaction") \
and valuation_rate == last_sle.get("valuation_rate"):
self.assertFalse(sle)
else:
self.assertEqual(sle[0].qty_after_transaction, qty_after_transaction)
self.assertEqual(sle[0].stock_value, qty_after_transaction * valuation_rate)
# no gl entries
self.assertTrue(frappe.db.get_value("Stock Ledger Entry",
{"voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name}))
self.assertFalse(get_stock_and_account_difference(["_Test Account Stock In Hand - _TC"]))
stock_reco.cancel()
self.assertFalse(frappe.db.get_value("Stock Ledger Entry",
{"voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name}))
self.assertFalse(frappe.db.get_value("GL Entry",
{"voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name}))
set_perpetual_inventory(0)
def get_stock_and_rate(self, force=False):
"""get stock and incoming rate on posting date"""
raw_material_cost = 0.0
if not self.posting_date or not self.posting_time:
frappe.throw(_("Posting date and posting time is mandatory"))
allow_negative_stock = cint(frappe.db.get_default("allow_negative_stock"))
for d in self.get('mtn_details'):
d.transfer_qty = flt(d.transfer_qty)
args = frappe._dict({
"item_code": d.item_code,
"warehouse": d.s_warehouse or d.t_warehouse,
"posting_date": self.posting_date,
"posting_time": self.posting_time,
"qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty,
"serial_no": d.serial_no
})
# get actual stock at source warehouse
d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0
# validate qty during submit
if d.docstatus==1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
frappe.throw(_("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}.
Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse,
self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty))
# get incoming rate
if not d.bom_no:
if not flt(d.incoming_rate) or d.s_warehouse or self.purpose == "Sales Return" or force:
incoming_rate = flt(self.get_incoming_rate(args), self.precision("incoming_rate", d))
if incoming_rate > 0:
d.incoming_rate = incoming_rate
d.amount = flt(flt(d.transfer_qty) * flt(d.incoming_rate), self.precision("amount", d))
if not d.t_warehouse:
raw_material_cost += flt(d.amount)
# set incoming rate for fg item
if self.purpose in ["Manufacture", "Repack"]:
number_of_fg_items = len([t.t_warehouse for t in self.get("mtn_details") if t.t_warehouse])
for d in self.get("mtn_details"):
if d.bom_no or (d.t_warehouse and number_of_fg_items == 1):
if not flt(d.incoming_rate) or force:
operation_cost_per_unit = 0
if d.bom_no:
bom = frappe.db.get_value("BOM", d.bom_no, ["operating_cost", "quantity"], as_dict=1)
operation_cost_per_unit = flt(bom.operating_cost) / flt(bom.quantity)
d.incoming_rate = flt(operation_cost_per_unit +
(raw_material_cost + flt(self.total_fixed_cost)) / flt(d.transfer_qty), self.precision("incoming_rate", d))
d.amount = flt(flt(d.transfer_qty) * flt(d.incoming_rate), self.precision("transfer_qty", d))
break
def get_qty_after_transaction(**args):
args = frappe._dict(args)
last_sle = get_previous_sle({
"item_code": args.item_code or "_Test Item",
"warehouse": args.warehouse or "_Test Warehouse - _TC",
"posting_date": args.posting_date or nowdate(),
"posting_time": args.posting_time or nowtime()
})
return flt(last_sle.get("qty_after_transaction"))
def get_warehouse_details(self, args):
ret = {}
if args.get("warehouse") and args.get("item_code"):
args.update({"posting_date": self.posting_date, "posting_time": self.posting_time})
args = frappe._dict(args)
ret = {
"actual_qty": get_previous_sle(args).get("qty_after_transaction") or 0,
"basic_rate": get_incoming_rate(args),
}
return ret
def get_warehouse_details(args):
if isinstance(args, basestring):
args = json.loads(args)
args = frappe._dict(args)
ret = {}
if args.warehouse and args.item_code:
args.update({"posting_date": args.posting_date, "posting_time": args.posting_time})
ret = {
"actual_qty": get_previous_sle(args).get("qty_after_transaction") or 0,
"basic_rate": get_incoming_rate(args),
}
return ret
def get_opening_balance(filters, columns):
if not (filters.item_code and filters.warehouse and filters.from_date):
return
from erpnext.stock.stock_ledger import get_previous_sle
last_entry = get_previous_sle({
"item_code": filters.item_code,
"warehouse_condition": get_warehouse_condition(filters.warehouse),
"posting_date": filters.from_date,
"posting_time": "00:00:00"
})
row = [""]*len(columns)
row[1] = _("'Opening'")
for i, v in ((9, 'qty_after_transaction'), (11, 'valuation_rate'), (12, 'stock_value')):
row[i] = last_entry.get(v, 0)
return row
def get_stock_and_rate(self, force=False):
"""get stock and incoming rate on posting date"""
raw_material_cost = 0.0
if not self.posting_date or not self.posting_time:
frappe.throw(_("Posting date and posting time is mandatory"))
allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock"))
for d in self.get('items'):
d.transfer_qty = flt(d.transfer_qty)
args = frappe._dict({
"item_code": d.item_code,
"warehouse": d.s_warehouse or d.t_warehouse,
"posting_date": self.posting_date,
"posting_time": self.posting_time,
"qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty,
"serial_no": d.serial_no,
})
# get actual stock at source warehouse
d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0
# validate qty during submit
if d.docstatus==1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
frappe.throw(_("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}.
Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse,
self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty), NegativeStockError)
# get incoming rate
if not d.bom_no:
if not flt(d.incoming_rate) or d.s_warehouse or self.purpose == "Sales Return" or force:
incoming_rate = flt(self.get_incoming_rate(args), self.precision("incoming_rate", d))
if incoming_rate > 0:
d.incoming_rate = incoming_rate
d.amount = flt(flt(d.transfer_qty) * flt(d.incoming_rate), d.precision("amount"))
if not d.t_warehouse:
raw_material_cost += flt(d.amount)
self.add_operation_cost(raw_material_cost, force)
def test_delivery_note_no_gl_entry(self):
company = frappe.db.get_value('Warehouse', '_Test Warehouse - _TC', 'company')
set_perpetual_inventory(0, company)
make_stock_entry(target="_Test Warehouse - _TC", qty=5, basic_rate=100)
stock_queue = json.loads(get_previous_sle({
"item_code": "_Test Item",
"warehouse": "_Test Warehouse - _TC",
"posting_date": nowdate(),
"posting_time": nowtime()
}).stock_queue or "[]")
dn = create_delivery_note()
sle = frappe.get_doc("Stock Ledger Entry", {"voucher_type": "Delivery Note", "voucher_no": dn.name})
self.assertEqual(sle.stock_value_difference, -1*stock_queue[0][1])
self.assertFalse(get_gl_entries("Delivery Note", dn.name))
def get_opening_balance(filters, columns):
if not (filters.item_code and filters.warehouse and filters.from_date):
return
from erpnext.stock.stock_ledger import get_previous_sle
last_entry = get_previous_sle({
"item_code": filters.item_code,
"warehouse": filters.warehouse,
"posting_date": filters.from_date,
"posting_time": "00:00:00"
})
row = [""] * len(columns)
row[1] = _("'Opening'")
for i, v in ((9, 'qty_after_transaction'), (11, 'valuation_rate'),
(12, 'stock_value')):
row[i] = last_entry.get(v, 0)
return row
def set_actual_qty(self):
allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock"))
for d in self.get('items'):
previous_sle = get_previous_sle({
"item_code": d.item_code,
"warehouse": d.s_warehouse or d.t_warehouse,
"posting_date": self.posting_date,
"posting_time": self.posting_time
})
# get actual stock at source warehouse
d.actual_qty = previous_sle.get("qty_after_transaction") or 0
# validate qty during submit
if d.docstatus==1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
frappe.throw(_("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}.
Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse,
self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty), NegativeStockError)
def get_rate(item_code, warehouse, based_on, price_list):
time = frappe.utils.now_datetime().time()
date = frappe.utils.now_datetime().date()
balance = 0
if warehouse:
previous_sle = get_previous_sle({
"item_code": item_code,
"warehouse": warehouse,
"posting_date": date,
"posting_time": time
})
# get actual stock at source warehouse
balance = previous_sle.get("qty_after_transaction") or 0
condition = ""
if price_list == "Standard Buying":
condition += " and buying = 1 "
elif price_list == "Standard Selling":
condition += " and selling = 1 "
query = """ SELECT * FROM `tabItem Price` WHERE item_code=%s {0} ORDER BY valid_from DESC LIMIT 1""".format(
condition)
item_price = frappe.db.sql(query, item_code, as_dict=1)
rate = item_price[0].price_list_rate if len(item_price) > 0 else 0
print(based_on)
if based_on == "Valuation Rate":
print("WALA DIR")
item_record = frappe.db.sql(
""" SELECT * FROM `tabItem` WHERE item_code=%s""",
item_code,
as_dict=1)
rate = item_record[0].valuation_rate if len(item_record) > 0 else 0
if based_on == "Last Purchase Rate":
print("WALA DIR")
item_record = frappe.db.sql(
""" SELECT * FROM `tabItem` WHERE item_code=%s""",
item_code,
as_dict=1)
rate = item_record[0].last_purchase_rate if len(item_record) > 0 else 0
return rate, balance
def get_warehouse_details(args):
if isinstance(args, basestring):
args = json.loads(args)
args = frappe._dict(args)
ret = {}
if args.warehouse and args.item_code:
args.update({
"posting_date": args.posting_date,
"posting_time": args.posting_time,
})
ret = {
"actual_qty": get_previous_sle(args).get("qty_after_transaction")
or 0,
"basic_rate": get_incoming_rate(args)
}
return ret
def get_incoming_rate(args):
"""Get Incoming Rate based on valuation method"""
from erpnext.stock.stock_ledger import get_previous_sle
in_rate = 0
if (args.get("serial_no") or "").strip():
in_rate = get_avg_purchase_rate(args.get("serial_no"))
else:
valuation_method = get_valuation_method(args.get("item_code"))
previous_sle = get_previous_sle(args)
if valuation_method == 'FIFO':
if not previous_sle:
return 0.0
previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]')
in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0
elif valuation_method == 'Moving Average':
in_rate = previous_sle.get('valuation_rate') or 0
return in_rate
def get_incoming_rate(args):
"""Get Incoming Rate based on valuation method"""
from erpnext.stock.stock_ledger import get_previous_sle
in_rate = 0
if (args.get("serial_no") or "").strip():
in_rate = get_avg_purchase_rate(args.get("serial_no"))
else:
valuation_method = get_valuation_method(args.get("item_code"))
previous_sle = get_previous_sle(args)
if valuation_method == 'FIFO':
if not previous_sle:
return 0.0
previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]')
in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0
elif valuation_method == 'Moving Average':
in_rate = previous_sle.get('valuation_rate') or 0
return in_rate
def get_stock_and_rate(self):
"""get stock and incoming rate on posting date"""
for d in self.get('mtn_details'):
args = frappe._dict({
"item_code": d.item_code,
"warehouse": d.s_warehouse or d.t_warehouse,
"posting_date": self.posting_date,
"posting_time": self.posting_time,
"qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty,
"serial_no": d.serial_no,
"bom_no": d.bom_no,
})
# get actual stock at source warehouse
d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0
# get incoming rate
if not flt(d.incoming_rate):
d.incoming_rate = self.get_incoming_rate(args)
d.amount = flt(d.transfer_qty) * flt(d.incoming_rate)
def get_stock_balance(item_code, warehouse, posting_date=None, posting_time=None, with_valuation_rate=False):
"""Returns stock balance quantity at given warehouse on given posting date or current date.
If `with_valuation_rate` is True, will return tuple (qty, rate)"""
from erpnext.stock.stock_ledger import get_previous_sle
if not posting_date: posting_date = nowdate()
if not posting_time: posting_time = nowtime()
last_entry = get_previous_sle({
"item_code": item_code,
"warehouse":warehouse,
"posting_date": posting_date,
"posting_time": posting_time })
if with_valuation_rate:
return (last_entry.qty_after_transaction, last_entry.valuation_rate) if last_entry else (0.0, 0.0)
else:
return last_entry.qty_after_transaction if last_entry else 0.0
def get_stock_and_rate(self):
"""get stock and incoming rate on posting date"""
for d in getlist(self.doclist, 'mtn_details'):
args = frappe._dict({
"item_code": d.item_code,
"warehouse": d.s_warehouse or d.t_warehouse,
"posting_date": self.doc.posting_date,
"posting_time": self.doc.posting_time,
"qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty,
"serial_no": d.serial_no,
"bom_no": d.bom_no,
})
# get actual stock at source warehouse
d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0
# get incoming rate
if not flt(d.incoming_rate):
d.incoming_rate = self.get_incoming_rate(args)
d.amount = flt(d.transfer_qty) * flt(d.incoming_rate)
def get_opening_balance(filters, columns):
if not (filters.item_code and filters.warehouse and filters.from_date):
return
from erpnext.stock.stock_ledger import get_previous_sle
last_entry = get_previous_sle({
"item_code": filters.item_code,
"warehouse_condition": get_warehouse_condition(filters.warehouse),
"posting_date": filters.from_date,
"posting_time": "00:00:00"
})
row = {
"item_code": _("'Opening'"),
"qty_after_transaction": last_entry.get("qty_after_transaction", 0),
"valuation_rate": last_entry.get("valuation_rate", 0),
"stock_value": last_entry.get("stock_value", 0)
}
return row
def get_stock_balance(item_code, warehouse, posting_date=None, posting_time=None, with_valuation_rate=False):
"""Returns stock balance quantity at given warehouse on given posting date or current date.
If `with_valuation_rate` is True, will return tuple (qty, rate)"""
from erpnext.stock.stock_ledger import get_previous_sle
if not posting_date: posting_date = nowdate()
if not posting_time: posting_time = nowtime()
last_entry = get_previous_sle({
"item_code": item_code,
"warehouse":warehouse,
"posting_date": posting_date,
"posting_time": posting_time })
if with_valuation_rate:
return (last_entry.qty_after_transaction, last_entry.valuation_rate) if last_entry else (0.0, 0.0)
else:
return last_entry.qty_after_transaction or 0.0
def radpp_make_entry(self, args, allow_negative_stock=False, via_landed_cost_voucher=False):
args.update({"doctype": "Stock Ledger Entry"})
sle = frappe.get_doc(args)
sle.flags.ignore_permissions = 1
sle.allow_negative_stock=allow_negative_stock
sle.via_landed_cost_voucher = via_landed_cost_voucher
sle.valuation_rate = flt(sle.valuation_rate)
sle.insert()
parent = frappe.get_doc(sle.voucher_type,sle.voucher_no)
# assert
previous_sle_args = {
"item_code": sle.item_code,
"warehouse": sle.warehouse,
"posting_date": parent.posting_date,
"posting_time": parent.posting_time,
"sle": sle.name
}
from erpnext.stock.stock_ledger import get_previous_sle
previous_sle = get_previous_sle(previous_sle_args)
prev_stock_value = (previous_sle.stock_value or 0.0) if previous_sle else 0.0
sle.valuation_rate = args['valuation_rate']
sle.qty_after_transaction = args['qty_after_transaction']
sle.stock_queue = [[sle.qty_after_transaction, sle.valuation_rate]]
sle.stock_value = flt(sle.qty_after_transaction) * flt(sle.valuation_rate)
# rounding as per precision
sle.stock_value = flt(sle.stock_value, sle.precision)
stock_value_difference = sle.stock_value - prev_stock_value
sle.prev_stock_value = sle.stock_value
# update current sle
sle.stock_queue = json.dumps(sle.stock_queue)
sle.stock_value_difference = stock_value_difference
frappe.get_doc(sle).db_update()
sle.save()
sle.submit()
return sle.name
def get_opening_balance(filters, _columns, date, balance_type, item_code):
if not (item_code and filters.warehouse and date):
return
from erpnext.stock.stock_ledger import get_previous_sle
last_entry = get_previous_sle({
"item_code":
item_code,
"warehouse_condition":
get_warehouse_condition(filters.warehouse),
"posting_date":
date,
"posting_time":
"00:00:00"
})
row = {}
row["voucher_type"] = _(balance_type)
for dummy, v in ((9, 'actual_qty')):
row[v] = last_entry.get(v, 0)
return row
def get_stock_balance(item_code,
warehouse,
posting_date=None,
posting_time=None,
with_valuation_rate=False,
with_serial_no=False):
"""Returns stock balance quantity at given warehouse on given posting date or current date.
If `with_valuation_rate` is True, will return tuple (qty, rate)"""
from erpnext.stock.stock_ledger import get_previous_sle
if not posting_date: posting_date = nowdate()
if not posting_time: posting_time = nowtime()
args = {
"item_code": item_code,
"warehouse": warehouse,
"posting_date": posting_date,
"posting_time": posting_time
}
last_entry = get_previous_sle(args)
if with_valuation_rate:
if with_serial_no:
serial_nos = last_entry.get("serial_no")
if (serial_nos and len(get_serial_nos_data(serial_nos)) <
last_entry.qty_after_transaction):
serial_nos = get_serial_nos_data_after_transactions(args)
return ((last_entry.qty_after_transaction,
last_entry.valuation_rate, serial_nos) if last_entry else
(0.0, 0.0, 0.0))
else:
return (last_entry.qty_after_transaction,
last_entry.valuation_rate) if last_entry else (0.0, 0.0)
else:
return last_entry.qty_after_transaction if last_entry else 0.0
def insert_stock_ledger_entries(self):
""" find difference between current and expected entries
and create stock ledger entries based on the difference"""
from erpnext.stock.utils import get_valuation_method
from erpnext.stock.stock_ledger import get_previous_sle
row_template = ["item_code", "warehouse", "qty", "valuation_rate"]
if not self.doc.reconciliation_json:
msgprint(_("""Stock Reconciliation file not uploaded"""), raise_exception=1)
data = json.loads(self.doc.reconciliation_json)
for row_num, row in enumerate(data[data.index(self.head_row)+1:]):
row = frappe._dict(zip(row_template, row))
row["row_num"] = row_num
previous_sle = get_previous_sle({
"item_code": row.item_code,
"warehouse": row.warehouse,
"posting_date": self.doc.posting_date,
"posting_time": self.doc.posting_time
})
# check valuation rate mandatory
if row.qty != "" and not row.valuation_rate and \
flt(previous_sle.get("qty_after_transaction")) <= 0:
frappe.throw(_("As existing qty for item: ") + row.item_code +
_(" at warehouse: ") + row.warehouse +
_(" is less than equals to zero in the system, valuation rate is mandatory for this item"))
change_in_qty = row.qty != "" and \
(flt(row.qty) - flt(previous_sle.get("qty_after_transaction")))
change_in_rate = row.valuation_rate != "" and \
(flt(row.valuation_rate) - flt(previous_sle.get("valuation_rate")))
if get_valuation_method(row.item_code) == "Moving Average":
self.sle_for_moving_avg(row, previous_sle, change_in_qty, change_in_rate)
else:
self.sle_for_fifo(row, previous_sle, change_in_qty, change_in_rate)
def update_stock_ledger(self):
""" find difference between current and expected entries
and create stock ledger entries based on the difference"""
from erpnext.stock.stock_ledger import get_previous_sle
sl_entries = []
for row in self.items:
item = frappe.get_doc("Item", row.item_code)
if item.has_serial_no or item.has_batch_no:
self.get_sle_for_serialized_items(row, sl_entries)
else:
previous_sle = get_previous_sle({
"item_code": row.item_code,
"warehouse": row.warehouse,
"posting_date": self.posting_date,
"posting_time": self.posting_time
})
if previous_sle:
if row.qty in ("", None):
row.qty = previous_sle.get("qty_after_transaction", 0)
if row.valuation_rate in ("", None):
row.valuation_rate = previous_sle.get("valuation_rate", 0)
if row.qty and not row.valuation_rate:
frappe.throw(
_("Valuation Rate required for Item {0} at row {1}").
format(row.item_code, row.idx))
if ((previous_sle
and row.qty == previous_sle.get("qty_after_transaction") and
(row.valuation_rate == previous_sle.get("valuation_rate")
or row.qty == 0)) or (not previous_sle and not row.qty)):
continue
sl_entries.append(self.get_sle_for_items(row))
if sl_entries:
self.make_sl_entries(sl_entries)
def execute(filters=None):
columns, data = get_columns(filters), []
time = frappe.utils.now_datetime().time()
date = frappe.utils.now_datetime().date()
average_stock_dates = get_dates(filters.get("average_stock"))
item_group = filters.get("item_group")
item = filters.get("item")
condition = ""
if item_group:
condition += " and item_group='{0}'".format(item_group)
if item:
condition += " and name='{0}'".format(item)
query = """ SELECT * FROM `tabItem` WHERE disabled=0 {0}""".format(
condition)
items = frappe.db.sql(query, as_dict=1)
for item in items:
query = """ SELECT *
FROM `tabStock Ledger Entry`
WHERE item_code='{0}' and actual_qty < 0""".format(item.name)
sle_check = frappe.db.sql(query, as_dict=1)
if len(sle_check) > 0:
obj = {
"item_code": item.name,
}
get_sle(item.name, average_stock_dates, obj)
obj['current_stock'] = get_previous_sle({
"item_code": item.name,
"posting_date": date,
"posting_time": time
}).get("qty_after_transaction") or 0
obj['stock_position'] = obj['current_stock'] - obj[
'average_sell_stock']
obj['stock_month'] = obj['current_stock'] / (
obj['average_sale_per_month']
if obj['average_sale_per_month'] > 0 else 1)
data.append(obj)
print(data)
return columns, data
def get_incoming_rate(args):
"""Get Incoming Rate based on valuation method"""
if isinstance(args, basestring):
args = json.loads(args)
in_rate = 0
batch_wise_rate = cint(
frappe.db.get_single_value(
"Stock Settings", 'exact_cost_valuation_for_batch_wise_items'))
if args.get("batch_no") and batch_wise_rate:
in_rate = get_batch_rate(args.get("batch_no"))
elif (args.get("serial_no") or "").strip():
in_rate = get_avg_purchase_rate(args.get("serial_no"))
else:
valuation_method = get_valuation_method(args.get("item_code"))
previous_sle = get_previous_sle(args)
if valuation_method == 'FIFO':
if previous_sle:
previous_stock_queue = json.loads(
previous_sle.get('stock_queue', '[]') or '[]')
in_rate = get_fifo_rate(previous_stock_queue,
args.get("qty")
or 0) if previous_stock_queue else 0
elif valuation_method == 'Moving Average':
in_rate = previous_sle.get('valuation_rate') or 0
if not in_rate:
voucher_no = args.get('voucher_no') or args.get('name')
in_rate = get_valuation_rate(args.get('item_code'),
args.get('warehouse'),
args.get('voucher_type'),
voucher_no,
args.get('allow_zero_valuation'),
currency=erpnext.get_company_currency(
args.get('company')),
company=args.get('company'))
return in_rate
def set_actual_qty(self):
allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock"))
for d in self.get('items'):
previous_sle = get_previous_sle({
"item_code": d.item_code,
"warehouse": d.s_warehouse or d.t_warehouse,
"posting_date": self.posting_date,
"posting_time": self.posting_time
})
# get actual stock at source warehouse
d.actual_qty = previous_sle.get("qty_after_transaction") or 0
# validate qty during submit
if d.docstatus==1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
frappe.throw(_("Row {0}: Qty not available for {4} in warehouse {1} at posting time of the entry ({2} {3})").format(d.idx,
frappe.bold(d.s_warehouse), formatdate(self.posting_date),
format_time(self.posting_time), frappe.bold(d.item_code))
+ '<br><br>' + _("Available qty is {0}, you need {1}").format(frappe.bold(d.actual_qty),
frappe.bold(d.transfer_qty)),
NegativeStockError, title=_('Insufficient Stock'))
def set_actual_qty(self):
allow_negative_stock = cint(
frappe.db.get_value("Stock Settings", None,
"allow_negative_stock"))
allow_start = True
for d in self.get('items'):
previous_sle = get_previous_sle({
"item_code": d.item_code,
"warehouse": self.warehouse,
"posting_date": nowdate(),
"posting_time": nowtime()
})
# get actual stock at source warehouse
d.actual_qty = previous_sle.get("qty_after_transaction") or 0
# validate qty
if not allow_negative_stock and d.actual_qty < d.qty:
allow_start = False
return allow_start
def insert_stock_ledger_entries(self):
""" find difference between current and expected entries
and create stock ledger entries based on the difference"""
from erpnext.stock.utils import get_valuation_method
from erpnext.stock.stock_ledger import get_previous_sle
row_template = ["item_code", "warehouse", "qty", "valuation_rate"]
if not self.reconciliation_json:
msgprint(_("""Stock Reconciliation file not uploaded"""), raise_exception=1)
data = json.loads(self.reconciliation_json)
for row_num, row in enumerate(data[data.index(self.head_row)+1:]):
row = frappe._dict(zip(row_template, row))
row["row_num"] = row_num
previous_sle = get_previous_sle({
"item_code": row.item_code,
"warehouse": row.warehouse,
"posting_date": self.posting_date,
"posting_time": self.posting_time
})
# check valuation rate mandatory
if row.qty != "" and not row.valuation_rate and \
flt(previous_sle.get("qty_after_transaction")) <= 0:
frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code))
change_in_qty = row.qty != "" and \
(flt(row.qty) - flt(previous_sle.get("qty_after_transaction")))
change_in_rate = row.valuation_rate != "" and \
(flt(row.valuation_rate) - flt(previous_sle.get("valuation_rate")))
if get_valuation_method(row.item_code) == "Moving Average":
self.sle_for_moving_avg(row, previous_sle, change_in_qty, change_in_rate)
else:
self.sle_for_fifo(row, previous_sle, change_in_qty, change_in_rate)
def update_stock_ledger(self):
""" find difference between current and expected entries
and create stock ledger entries based on the difference"""
from erpnext.stock.stock_ledger import get_previous_sle
row_template = ["item_code", "warehouse", "qty", "valuation_rate"]
if not self.reconciliation_json:
msgprint(_("""Stock Reconciliation file not uploaded"""),
raise_exception=1)
data = json.loads(self.reconciliation_json)
for row_num, row in enumerate(data[data.index(self.head_row) + 1:]):
row = frappe._dict(zip(row_template, row))
row["row_num"] = row_num
if row.qty in ("", None) or row.valuation_rate in ("", None):
previous_sle = get_previous_sle({
"item_code":
row.item_code,
"warehouse":
row.warehouse,
"posting_date":
self.posting_date,
"posting_time":
self.posting_time
})
if row.qty in ("", None):
row.qty = previous_sle.get("qty_after_transaction")
if row.valuation_rate in ("", None):
row.valuation_rate = previous_sle.get("valuation_rate")
# if row.qty and not row.valuation_rate:
# frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code))
self.insert_entries(row)
def get_incoming_rate(args):
"""Get Incoming Rate based on valuation method"""
from erpnext.stock.stock_ledger import get_previous_sle
in_rate = 0
if args.get("serial_no"):
in_rate = get_avg_purchase_rate(args.get("serial_no"))
elif args.get("bom_no"):
result = frappe.db.sql("""select ifnull(total_cost, 0) / ifnull(quantity, 1)
from `tabBOM` where name = %s and docstatus=1 and is_active=1""", args.get("bom_no"))
in_rate = result and flt(result[0][0]) or 0
else:
valuation_method = get_valuation_method(args.get("item_code"))
previous_sle = get_previous_sle(args)
if valuation_method == 'FIFO':
if not previous_sle:
return 0.0
previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]')
in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0
elif valuation_method == 'Moving Average':
in_rate = previous_sle.get('valuation_rate') or 0
return in_rate
def get_incoming_rate(args, raise_error_if_no_rate=True):
"""Get Incoming Rate based on valuation method"""
from erpnext.stock.stock_ledger import get_previous_sle, get_valuation_rate
if isinstance(args, string_types):
args = json.loads(args)
in_rate = 0
if (args.get("serial_no") or "").strip():
in_rate = get_avg_purchase_rate(args.get("serial_no"))
else:
valuation_method = get_valuation_method(args.get("item_code"))
previous_sle = get_previous_sle(args)
if valuation_method == "FIFO":
if previous_sle:
previous_stock_queue = json.loads(
previous_sle.get("stock_queue", "[]") or "[]")
in_rate = (get_fifo_rate(previous_stock_queue,
args.get("qty") or 0)
if previous_stock_queue else 0)
elif valuation_method == "Moving Average":
in_rate = previous_sle.get("valuation_rate") or 0
if not in_rate:
voucher_no = args.get("voucher_no") or args.get("name")
in_rate = get_valuation_rate(
args.get("item_code"),
args.get("warehouse"),
args.get("voucher_type"),
voucher_no,
args.get("allow_zero_valuation"),
currency=erpnext.get_company_currency(args.get("company")),
company=args.get("company"),
raise_error_if_no_rate=raise_error_if_no_rate,
)
return flt(in_rate)
def add_exploded_items(self):
exploded_items = {}
for d in self.get('exploded_items'):
exploded_items[d.item_code] = d
self.set('exploded_items', [])
from erpnext.stock.doctype.stock_entry.stock_entry import get_best_warehouse
from erpnext.stock.stock_ledger import get_previous_sle
posting_date = nowdate()
posting_time = nowtime()
for d in sorted(self.cur_exploded_items, key=itemgetter(0)):
ch = self.append('exploded_items', {})
for i in self.cur_exploded_items[d].keys():
ch.set(i, self.cur_exploded_items[d][i])
best_warehouse,enough_stock = get_best_warehouse(ch.item_code,ch.stock_qty,company = self.company)
if best_warehouse:
ch.actual_qty = get_previous_sle({
"item_code": ch.item_code,
"warehouse": best_warehouse,
"posting_date": posting_date,
"posting_time": posting_time
}).get("qty_after_transaction") or 0
else:
ch.actual_qty = 0
ch.source_warehouse = best_warehouse
ch.amount = flt(ch.stock_qty) * flt(ch.rate)
ch.qty_consumed_per_unit = flt(ch.stock_qty)
if exploded_items.get(ch.item_code):
ch.dutible = exploded_items[ch.item_code].dutible
def get_incoming_rate(self, args):
incoming_rate = 0
if self.purpose == "Sales Return" and \
(self.delivery_note_no or self.sales_invoice_no):
sle = frappe.db.sql("""select name, posting_date, posting_time,
actual_qty, stock_value, warehouse from `tabStock Ledger Entry`
where voucher_type = %s and voucher_no = %s and
item_code = %s limit 1""",
((self.delivery_note_no and "Delivery Note" or "Sales Invoice"),
self.delivery_note_no or self.sales_invoice_no, args.item_code), as_dict=1)
if sle:
args.update({
"posting_date": sle[0].posting_date,
"posting_time": sle[0].posting_time,
"sle": sle[0].name,
"warehouse": sle[0].warehouse,
})
previous_sle = get_previous_sle(args)
incoming_rate = (flt(sle[0].stock_value) - flt(previous_sle.get("stock_value"))) / \
flt(sle[0].actual_qty)
else:
incoming_rate = get_incoming_rate(args)
return incoming_rate
def get_incoming_rate(self, args):
incoming_rate = 0
if self.doc.purpose == "Sales Return" and \
(self.doc.delivery_note_no or self.doc.sales_invoice_no):
sle = frappe.db.sql("""select name, posting_date, posting_time,
actual_qty, stock_value, warehouse from `tabStock Ledger Entry`
where voucher_type = %s and voucher_no = %s and
item_code = %s limit 1""",
((self.doc.delivery_note_no and "Delivery Note" or "Sales Invoice"),
self.doc.delivery_note_no or self.doc.sales_invoice_no, args.item_code), as_dict=1)
if sle:
args.update({
"posting_date": sle[0].posting_date,
"posting_time": sle[0].posting_time,
"sle": sle[0].name,
"warehouse": sle[0].warehouse,
})
previous_sle = get_previous_sle(args)
incoming_rate = (flt(sle[0].stock_value) - flt(previous_sle.get("stock_value"))) / \
flt(sle[0].actual_qty)
else:
incoming_rate = get_incoming_rate(args)
return incoming_rate
def get_stock_and_rate(self, force=False):
"""get stock and incoming rate on posting date"""
raw_material_cost = 0.0
if not self.posting_date or not self.posting_time:
frappe.throw(_("Posting date and posting time is mandatory"))
allow_negative_stock = cint(
frappe.db.get_value("Stock Settings", None,
"allow_negative_stock"))
for d in self.get('items'):
d.transfer_qty = flt(d.transfer_qty)
args = frappe._dict({
"item_code":
d.item_code,
"warehouse":
d.s_warehouse or d.t_warehouse,
"posting_date":
self.posting_date,
"posting_time":
self.posting_time,
"qty":
d.s_warehouse and -1 * d.transfer_qty or d.transfer_qty,
"serial_no":
d.serial_no,
})
# get actual stock at source warehouse
d.actual_qty = get_previous_sle(args).get(
"qty_after_transaction") or 0
# validate qty during submit
if d.docstatus == 1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
frappe.throw(
_("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}.
Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse,
self.posting_date,
self.posting_time,
d.actual_qty,
d.transfer_qty),
NegativeStockError)
# get incoming rate
if not d.bom_no:
if not flt(
d.incoming_rate
) or d.s_warehouse or self.purpose == "Sales Return" or force:
incoming_rate = flt(self.get_incoming_rate(args),
self.precision("incoming_rate", d))
if incoming_rate > 0:
d.incoming_rate = incoming_rate
d.amount = flt(
flt(d.transfer_qty) * flt(d.incoming_rate),
d.precision("amount"))
if not d.t_warehouse:
raw_material_cost += flt(d.amount)
self.add_operation_cost(raw_material_cost, force)
def _test_reco_sle_gle(self, valuation_method):
insert_existing_sle(warehouse='Stores - TCP1')
company = frappe.db.get_value('Warehouse', 'Stores - TCP1', 'company')
# [[qty, valuation_rate, posting_date,
# posting_time, expected_stock_value, bin_qty, bin_valuation]]
input_data = [[50, 1000], [25, 900], ["", 1000], [20, ""], [0, ""]]
for d in input_data:
set_valuation_method("_Test Item", valuation_method)
last_sle = get_previous_sle({
"item_code": "_Test Item",
"warehouse": "Stores - TCP1",
"posting_date": nowdate(),
"posting_time": nowtime()
})
# submit stock reconciliation
stock_reco = create_stock_reconciliation(
qty=d[0],
rate=d[1],
posting_date=nowdate(),
posting_time=nowtime(),
warehouse="Stores - TCP1",
company=company,
expense_account="Stock Adjustment - TCP1")
# check stock value
sle = frappe.db.sql("""select * from `tabStock Ledger Entry`
where voucher_type='Stock Reconciliation' and voucher_no=%s""",
stock_reco.name,
as_dict=1)
qty_after_transaction = flt(d[0]) if d[0] != "" else flt(
last_sle.get("qty_after_transaction"))
valuation_rate = flt(d[1]) if d[1] != "" else flt(
last_sle.get("valuation_rate"))
if qty_after_transaction == last_sle.get("qty_after_transaction") \
and valuation_rate == last_sle.get("valuation_rate"):
self.assertFalse(sle)
else:
self.assertEqual(flt(sle[0].qty_after_transaction, 1),
flt(qty_after_transaction, 1))
self.assertEqual(
flt(sle[0].stock_value, 1),
flt(qty_after_transaction * valuation_rate, 1))
# no gl entries
self.assertTrue(
frappe.db.get_value(
"Stock Ledger Entry", {
"voucher_type": "Stock Reconciliation",
"voucher_no": stock_reco.name
}))
acc_bal, stock_bal, wh_list = get_stock_and_account_balance(
"Stock In Hand - TCP1", stock_reco.posting_date,
stock_reco.company)
self.assertEqual(flt(acc_bal, 1), flt(stock_bal, 1))
stock_reco.cancel()
def update_stock_ledger(self):
""" find difference between current and expected entries
and create stock ledger entries based on the difference"""
from erpnext.stock.stock_ledger import get_previous_sle
sl_entries = []
has_serial_no = False
has_batch_no = False
for row in self.items:
item = frappe.get_doc("Item", row.item_code)
if item.has_batch_no:
has_batch_no = True
if item.has_serial_no or item.has_batch_no:
has_serial_no = True
self.get_sle_for_serialized_items(row, sl_entries)
else:
if row.serial_no or row.batch_no:
frappe.throw(_("Row #{0}: Item {1} is not a Serialized/Batched Item. It cannot have a Serial No/Batch No against it.") \
.format(row.idx, frappe.bold(row.item_code)))
previous_sle = get_previous_sle({
"item_code":
row.item_code,
"warehouse":
row.warehouse,
"posting_date":
self.posting_date,
"posting_time":
self.posting_time
})
if previous_sle:
if row.qty in ("", None):
row.qty = previous_sle.get("qty_after_transaction", 0)
if row.valuation_rate in ("", None):
row.valuation_rate = previous_sle.get(
"valuation_rate", 0)
if row.qty and not row.valuation_rate:
frappe.throw(
_("Valuation Rate required for Item {0} at row {1}").
format(row.item_code, row.idx))
if ((previous_sle
and row.qty == previous_sle.get("qty_after_transaction")
and
(row.valuation_rate == previous_sle.get("valuation_rate")
or row.qty == 0)) or (not previous_sle and not row.qty)):
continue
sl_entries.append(self.get_sle_for_items(row))
if sl_entries:
if has_serial_no:
sl_entries = self.merge_similar_item_serial_nos(sl_entries)
allow_negative_stock = False
if has_batch_no:
allow_negative_stock = True
self.make_sl_entries(sl_entries,
allow_negative_stock=allow_negative_stock)
if has_serial_no and sl_entries:
self.update_valuation_rate_for_serial_no()
def get_opening_balance(filters, columns):
#frappe.msgprint(frappe._('get_opening_balance ' + str(filters)))
if not (filters.item_code and filters.company):
return
warehouse_list = []
from erpnext.stock.stock_ledger import get_previous_sle
if not (filters.item_code and filters.warehouse and filters.company):
for warehouse in frappe.get_list("Warehouse", fields=["name"]):
last_sle_for_each_warehouse = get_previous_sle({
"item_code":
filters.item_code,
"warehouse":
warehouse['name'],
"posting_date":
datetime.datetime.now().date(),
"posting_time":
datetime.datetime.now().time()
})
if last_sle_for_each_warehouse:
warehouse_list.append({'warehouse': warehouse['name']})
else:
last_sle_for_each_warehouse = get_previous_sle({
"item_code":
filters.item_code,
"warehouse":
filters.warehouse,
"posting_date":
datetime.datetime.now().date(),
"posting_time":
datetime.datetime.now().time()
})
if last_sle_for_each_warehouse:
warehouse_list.append({'warehouse': filters.warehouse})
# frappe.msgprint(frappe._('len(warehouse_list) : ' + str(warehouse_list)))
last_sle_for_each_warehouse = [
get_previous_sle({
"item_code": filters.item_code,
"warehouse": warehouse['warehouse'],
"posting_date": datetime.datetime.now().date(),
"posting_time": datetime.datetime.now().time()
}) for warehouse in warehouse_list
]
rows = []
for index, balance in enumerate(last_sle_for_each_warehouse):
#frappe.msgprint(frappe._('balance[qty_after_transaction] : ' + str(balance.get('qty_after_transaction'))))
dict_sle_value = {
2: _("Opening"),
5: balance['qty_after_transaction'],
6: 0.0,
8: balance['warehouse']
}
if index > 0:
dict_sle_value[7] = balance[
'qty_after_transaction'] + last_sle_for_each_warehouse[
index - 1]['qty_after_transaction']
if index == 0:
dict_sle_value[7] = balance['qty_after_transaction']
if index < (len(last_sle_for_each_warehouse) - 1):
next_ = last_sle_for_each_warehouse[index + 1]
balance['qty_after_transaction'] = dict_sle_value[7]
rows.append([
dict_sle_value.get(x) if x in dict_sle_value else ""
for x in range(len(columns))
])
return rows
def get_stock_and_rate(self, force=False):
"""get stock and incoming rate on posting date"""
raw_material_cost = 0.0
if not self.posting_date or not self.posting_time:
frappe.throw(_("Posting date and posting time is mandatory"))
allow_negative_stock = cint(
frappe.db.get_default("allow_negative_stock"))
for d in self.get('mtn_details'):
d.transfer_qty = flt(d.transfer_qty)
args = frappe._dict({
"item_code":
d.item_code,
"warehouse":
d.s_warehouse or d.t_warehouse,
"posting_date":
self.posting_date,
"posting_time":
self.posting_time,
"qty":
d.s_warehouse and -1 * d.transfer_qty or d.transfer_qty,
"serial_no":
d.serial_no
})
# get actual stock at source warehouse
d.actual_qty = get_previous_sle(args).get(
"qty_after_transaction") or 0
# validate qty during submit
if d.docstatus == 1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
frappe.throw(
_("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}.
Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse,
self.posting_date,
self.posting_time,
d.actual_qty,
d.transfer_qty))
# get incoming rate
if not d.bom_no:
if not flt(
d.incoming_rate
) or d.s_warehouse or self.purpose == "Sales Return" or force:
incoming_rate = flt(self.get_incoming_rate(args),
self.precision("incoming_rate", d))
if incoming_rate > 0:
d.incoming_rate = incoming_rate
d.amount = flt(
flt(d.transfer_qty) * flt(d.incoming_rate),
self.precision("amount", d))
if not d.t_warehouse:
raw_material_cost += flt(d.amount)
# set incoming rate for fg item
if self.purpose in ["Manufacture", "Repack"]:
number_of_fg_items = len([
t.t_warehouse for t in self.get("mtn_details") if t.t_warehouse
])
for d in self.get("mtn_details"):
if d.bom_no or (d.t_warehouse and number_of_fg_items == 1):
if not flt(d.incoming_rate) or force:
operation_cost_per_unit = 0
if d.bom_no:
bom = frappe.db.get_value(
"BOM",
d.bom_no, ["operating_cost", "quantity"],
as_dict=1)
operation_cost_per_unit = flt(
bom.operating_cost) / flt(bom.quantity)
d.incoming_rate = flt(
operation_cost_per_unit +
(raw_material_cost + flt(self.total_fixed_cost)) /
flt(d.transfer_qty),
self.precision("incoming_rate", d))
d.amount = flt(
flt(d.transfer_qty) * flt(d.incoming_rate),
self.precision("transfer_qty", d))
break
def get_sle_for_serialized_items(self, row, sl_entries):
from erpnext.stock.stock_ledger import get_previous_sle
serial_nos = get_serial_nos(row.serial_no)
# To issue existing serial nos
if row.current_qty and (row.current_serial_no or row.batch_no):
args = self.get_sle_for_items(row)
args.update({
'actual_qty': -1 * row.current_qty,
'serial_no': row.current_serial_no,
'batch_no': row.batch_no,
'valuation_rate': row.current_valuation_rate
})
if row.current_serial_no:
args.update({
'qty_after_transaction': 0,
})
sl_entries.append(args)
for serial_no in serial_nos:
args = self.get_sle_for_items(row, [serial_no])
previous_sle = get_previous_sle({
"item_code": row.item_code,
"posting_date": self.posting_date,
"posting_time": self.posting_time,
"serial_no": serial_no
})
if previous_sle and row.warehouse != previous_sle.get("warehouse"):
# If serial no exists in different warehouse
new_args = args.copy()
new_args.update({
'actual_qty':
-1,
'qty_after_transaction':
cint(previous_sle.get('qty_after_transaction')) - 1,
'warehouse':
previous_sle.get("warehouse", '') or row.warehouse,
'valuation_rate':
previous_sle.get("valuation_rate")
})
sl_entries.append(new_args)
if row.qty:
args = self.get_sle_for_items(row)
args.update({
'actual_qty': row.qty,
'incoming_rate': row.valuation_rate,
'valuation_rate': row.valuation_rate
})
sl_entries.append(args)
if serial_nos == get_serial_nos(row.current_serial_no):
# update valuation rate
self.update_valuation_rate_for_serial_nos(row, serial_nos)
def test_item_cost_reposting(self):
company = "_Test Company"
# _Test Item for Reposting at Stores warehouse on 10-04-2020: Qty = 50, Rate = 100
create_stock_reconciliation(item_code="_Test Item for Reposting",
warehouse="Stores - _TC",
qty=50,
rate=100,
company=company,
expense_account="Stock Adjustment - _TC"
if frappe.get_all("Stock Ledger Entry")
else "Temporary Opening - _TC",
posting_date='2020-04-10',
posting_time='14:00')
# _Test Item for Reposting at FG warehouse on 20-04-2020: Qty = 10, Rate = 200
create_stock_reconciliation(item_code="_Test Item for Reposting",
warehouse="Finished Goods - _TC",
qty=10,
rate=200,
company=company,
expense_account="Stock Adjustment - _TC"
if frappe.get_all("Stock Ledger Entry")
else "Temporary Opening - _TC",
posting_date='2020-04-20',
posting_time='14:00')
# _Test Item for Reposting transferred from Stores to FG warehouse on 30-04-2020
se = make_stock_entry(item_code="_Test Item for Reposting",
source="Stores - _TC",
target="Finished Goods - _TC",
company=company,
qty=10,
expense_account="Stock Adjustment - _TC"
if frappe.get_all("Stock Ledger Entry") else
"Temporary Opening - _TC",
posting_date='2020-04-30',
posting_time='14:00')
target_wh_sle = frappe.db.get_value('Stock Ledger Entry', {
"item_code": "_Test Item for Reposting",
"warehouse": "Finished Goods - _TC",
"voucher_type": "Stock Entry",
"voucher_no": se.name
}, ["valuation_rate"],
as_dict=1)
self.assertEqual(target_wh_sle.get("valuation_rate"), 150)
# Repack entry on 5-5-2020
repack = create_repack_entry(company=company,
posting_date='2020-05-05',
posting_time='14:00')
finished_item_sle = frappe.db.get_value('Stock Ledger Entry', {
"item_code": "_Test Finished Item for Reposting",
"warehouse": "Finished Goods - _TC",
"voucher_type": "Stock Entry",
"voucher_no": repack.name
}, ["incoming_rate", "valuation_rate"],
as_dict=1)
self.assertEqual(finished_item_sle.get("incoming_rate"), 540)
self.assertEqual(finished_item_sle.get("valuation_rate"), 540)
# Reconciliation for _Test Item for Reposting at Stores on 12-04-2020: Qty = 50, Rate = 150
sr = create_stock_reconciliation(
item_code="_Test Item for Reposting",
warehouse="Stores - _TC",
qty=50,
rate=150,
company=company,
expense_account="Stock Adjustment - _TC"
if frappe.get_all("Stock Ledger Entry") else
"Temporary Opening - _TC",
posting_date='2020-04-12',
posting_time='14:00')
# Check valuation rate of finished goods warehouse after back-dated entry at Stores
target_wh_sle = get_previous_sle({
"item_code": "_Test Item for Reposting",
"warehouse": "Finished Goods - _TC",
"posting_date": '2020-04-30',
"posting_time": '14:00'
})
self.assertEqual(target_wh_sle.get("incoming_rate"), 150)
self.assertEqual(target_wh_sle.get("valuation_rate"), 175)
# Check valuation rate of repacked item after back-dated entry at Stores
finished_item_sle = frappe.db.get_value('Stock Ledger Entry', {
"item_code": "_Test Finished Item for Reposting",
"warehouse": "Finished Goods - _TC",
"voucher_type": "Stock Entry",
"voucher_no": repack.name
}, ["incoming_rate", "valuation_rate"],
as_dict=1)
self.assertEqual(finished_item_sle.get("incoming_rate"), 790)
self.assertEqual(finished_item_sle.get("valuation_rate"), 790)
# Check updated rate in Repack entry
repack.reload()
self.assertEqual(repack.items[0].get("basic_rate"), 150)
self.assertEqual(repack.items[1].get("basic_rate"), 750)
def _test_reco_sle_gle(self, valuation_method):
set_perpetual_inventory()
# [[qty, valuation_rate, posting_date,
# posting_time, expected_stock_value, bin_qty, bin_valuation]]
input_data = [[50, 1000, "2012-12-26", "12:00"],
[25, 900, "2012-12-26", "12:00"],
["", 1000, "2012-12-20", "12:05"],
[20, "", "2012-12-26", "12:05"],
[0, "", "2012-12-31", "12:10"]]
for d in input_data:
set_valuation_method("_Test Item", valuation_method)
last_sle = get_previous_sle({
"item_code": "_Test Item",
"warehouse": "_Test Warehouse - _TC",
"posting_date": d[2],
"posting_time": d[3]
})
# submit stock reconciliation
stock_reco = create_stock_reconciliation(qty=d[0],
rate=d[1],
posting_date=d[2],
posting_time=d[3])
# check stock value
sle = frappe.db.sql("""select * from `tabStock Ledger Entry`
where voucher_type='Stock Reconciliation' and voucher_no=%s""",
stock_reco.name,
as_dict=1)
qty_after_transaction = flt(d[0]) if d[0] != "" else flt(
last_sle.get("qty_after_transaction"))
valuation_rate = flt(d[1]) if d[1] != "" else flt(
last_sle.get("valuation_rate"))
if qty_after_transaction == last_sle.get("qty_after_transaction") \
and valuation_rate == last_sle.get("valuation_rate"):
self.assertFalse(sle)
else:
self.assertEqual(sle[0].qty_after_transaction,
qty_after_transaction)
self.assertEqual(sle[0].stock_value,
qty_after_transaction * valuation_rate)
# no gl entries
self.assertTrue(
frappe.db.get_value(
"Stock Ledger Entry", {
"voucher_type": "Stock Reconciliation",
"voucher_no": stock_reco.name
}))
self.assertFalse(
get_stock_and_account_difference(
["_Test Account Stock In Hand - _TC"]))
stock_reco.cancel()
self.assertFalse(
frappe.db.get_value(
"Stock Ledger Entry", {
"voucher_type": "Stock Reconciliation",
"voucher_no": stock_reco.name
}))
self.assertFalse(
frappe.db.get_value(
"GL Entry", {
"voucher_type": "Stock Reconciliation",
"voucher_no": stock_reco.name
}))
set_perpetual_inventory(0)
