Merge pull request #2274 from nabinhait/stock_reco
Stock reco
diff --git a/erpnext/accounts/doctype/accounts_settings/accounts_settings.py b/erpnext/accounts/doctype/accounts_settings/accounts_settings.py
index f0890dd..7280322 100644
--- a/erpnext/accounts/doctype/accounts_settings/accounts_settings.py
+++ b/erpnext/accounts/doctype/accounts_settings/accounts_settings.py
@@ -14,6 +14,9 @@
frappe.db.set_default("auto_accounting_for_stock", self.auto_accounting_for_stock)
if cint(self.auto_accounting_for_stock):
+ if cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock")):
+ frappe.throw(_("Negative stock is not allowed in case of Perpetual Inventory, please disable it from Stock Settings"))
+
# set default perpetual account in company
for company in frappe.db.sql("select name from tabCompany"):
frappe.get_doc("Company", company[0]).save()
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
index a2bf78c..31f7113 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
@@ -7,7 +7,7 @@
from frappe.utils import cint, cstr, flt
from frappe import _, msgprint, throw
from erpnext.accounts.party import get_party_account, get_due_date
-from erpnext.controllers.stock_controller import update_gl_entries_after
+from erpnext.controllers.stock_controller import update_gl_entries_after, block_negative_stock
from frappe.model.mapper import get_mapped_doc
from erpnext.controllers.selling_controller import SellingController
@@ -456,8 +456,8 @@
self.make_sl_entries(sl_entries)
- def make_gl_entries(self, repost_future_gle=True):
- gl_entries = self.get_gl_entries()
+ def make_gl_entries(self, repost_future_gle=True, allow_negative_stock=False):
+ gl_entries = self.get_gl_entries(allow_negative_stock=allow_negative_stock)
if gl_entries:
from erpnext.accounts.general_ledger import make_gl_entries
@@ -476,7 +476,7 @@
items, warehouses = self.get_items_and_warehouses()
update_gl_entries_after(self.posting_date, self.posting_time, warehouses, items)
- def get_gl_entries(self, warehouse_account=None):
+ def get_gl_entries(self, warehouse_account=None, allow_negative_stock=False):
from erpnext.accounts.general_ledger import merge_similar_entries
gl_entries = []
@@ -485,7 +485,7 @@
self.make_tax_gl_entries(gl_entries)
- self.make_item_gl_entries(gl_entries)
+ self.make_item_gl_entries(gl_entries, allow_negative_stock)
# merge gl entries before adding pos entries
gl_entries = merge_similar_entries(gl_entries)
@@ -520,7 +520,7 @@
})
)
- def make_item_gl_entries(self, gl_entries):
+ def make_item_gl_entries(self, gl_entries, allow_negative_stock=False):
# income account gl entries
for item in self.get("entries"):
if flt(item.base_amount):
@@ -537,7 +537,7 @@
# expense account gl entries
if cint(frappe.defaults.get_global_default("auto_accounting_for_stock")) \
and cint(self.update_stock):
- gl_entries += super(SalesInvoice, self).get_gl_entries()
+ gl_entries += super(SalesInvoice, self).get_gl_entries(allow_negative_stock=allow_negative_stock)
def make_pos_gl_entries(self, gl_entries):
if cint(self.is_pos) and self.cash_bank_account and self.paid_amount:
diff --git a/erpnext/accounts/general_ledger.py b/erpnext/accounts/general_ledger.py
index 2114768..073ef8a 100644
--- a/erpnext/accounts/general_ledger.py
+++ b/erpnext/accounts/general_ledger.py
@@ -97,8 +97,7 @@
for entry in gl_map:
if entry.account in aii_accounts:
- frappe.throw(_("Account: {0} can only be updated via \
- Stock Transactions").format(entry.account), StockAccountInvalidTransaction)
+ frappe.throw(_("Account: {0} can only be updated via Stock Transactions").format(entry.account), StockAccountInvalidTransaction)
def delete_gl_entries(gl_entries=None, voucher_type=None, voucher_no=None,
diff --git a/erpnext/controllers/stock_controller.py b/erpnext/controllers/stock_controller.py
index 5755253..8c5bcac 100644
--- a/erpnext/controllers/stock_controller.py
+++ b/erpnext/controllers/stock_controller.py
@@ -8,10 +8,10 @@
import frappe.defaults
from erpnext.controllers.accounts_controller import AccountsController
-from erpnext.accounts.general_ledger import make_gl_entries, delete_gl_entries
+from erpnext.accounts.general_ledger import make_gl_entries, delete_gl_entries, process_gl_map
class StockController(AccountsController):
- def make_gl_entries(self, repost_future_gle=True):
+ def make_gl_entries(self, repost_future_gle=True, allow_negative_stock=False):
if self.docstatus == 2:
delete_gl_entries(voucher_type=self.doctype, voucher_no=self.name)
@@ -19,16 +19,19 @@
warehouse_account = get_warehouse_account()
if self.docstatus==1:
- gl_entries = self.get_gl_entries(warehouse_account)
+ gl_entries = self.get_gl_entries(warehouse_account, allow_negative_stock=allow_negative_stock)
make_gl_entries(gl_entries)
if repost_future_gle:
items, warehouses = self.get_items_and_warehouses()
- update_gl_entries_after(self.posting_date, self.posting_time, warehouses, items, warehouse_account)
+ update_gl_entries_after(self.posting_date, self.posting_time, warehouses, items,
+ warehouse_account, allow_negative_stock)
def get_gl_entries(self, warehouse_account=None, default_expense_account=None,
- default_cost_center=None):
- from erpnext.accounts.general_ledger import process_gl_map
+ default_cost_center=None, allow_negative_stock=False):
+
+ # block_negative_stock(allow_negative_stock)
+
if not warehouse_account:
warehouse_account = get_warehouse_account()
@@ -46,12 +49,17 @@
self.check_expense_account(detail)
+ stock_value_difference = flt(sle.stock_value_difference, 2)
+ if not stock_value_difference:
+ valuation_rate = get_valuation_rate(sle.item_code, sle.warehouse, sle.posting_date)
+ stock_value_difference = flt(sle.actual_qty)*flt(valuation_rate)
+
gl_list.append(self.get_gl_dict({
"account": warehouse_account[sle.warehouse],
"against": detail.expense_account,
"cost_center": detail.cost_center,
"remarks": self.get("remarks") or "Accounting Entry for Stock",
- "debit": flt(sle.stock_value_difference, 2)
+ "debit": stock_value_difference
}))
# to target warehouse / expense account
@@ -60,7 +68,7 @@
"against": warehouse_account[sle.warehouse],
"cost_center": detail.cost_center,
"remarks": self.get("remarks") or "Accounting Entry for Stock",
- "credit": flt(sle.stock_value_difference, 2)
+ "credit": stock_value_difference
}))
elif sle.warehouse not in warehouse_with_no_account:
warehouse_with_no_account.append(sle.warehouse)
@@ -118,7 +126,8 @@
def get_stock_ledger_details(self):
stock_ledger = {}
- for sle in frappe.db.sql("""select warehouse, stock_value_difference, voucher_detail_no
+ for sle in frappe.db.sql("""select warehouse, stock_value_difference,
+ voucher_detail_no, item_code, posting_date, actual_qty
from `tabStock Ledger Entry` where voucher_type=%s and voucher_no=%s""",
(self.doctype, self.name), as_dict=True):
stock_ledger.setdefault(sle.voucher_detail_no, []).append(sle)
@@ -214,7 +223,8 @@
return serialized_items
-def update_gl_entries_after(posting_date, posting_time, for_warehouses=None, for_items=None, warehouse_account=None):
+def update_gl_entries_after(posting_date, posting_time, for_warehouses=None, for_items=None,
+ warehouse_account=None, allow_negative_stock=False):
def _delete_gl_entries(voucher_type, voucher_no):
frappe.db.sql("""delete from `tabGL Entry`
where voucher_type=%s and voucher_no=%s""", (voucher_type, voucher_no))
@@ -228,12 +238,12 @@
for voucher_type, voucher_no in future_stock_vouchers:
existing_gle = gle.get((voucher_type, voucher_no), [])
voucher_obj = frappe.get_doc(voucher_type, voucher_no)
- expected_gle = voucher_obj.get_gl_entries(warehouse_account)
+ expected_gle = voucher_obj.get_gl_entries(warehouse_account, allow_negative_stock=allow_negative_stock)
if expected_gle:
if not existing_gle or not compare_existing_and_expected_gle(existing_gle,
expected_gle):
_delete_gl_entries(voucher_type, voucher_no)
- voucher_obj.make_gl_entries(repost_future_gle=False)
+ voucher_obj.make_gl_entries(repost_future_gle=False, allow_negative_stock=allow_negative_stock)
else:
_delete_gl_entries(voucher_type, voucher_no)
@@ -285,3 +295,22 @@
warehouse_account = dict(frappe.db.sql("""select master_name, name from tabAccount
where account_type = 'Warehouse' and ifnull(master_name, '') != ''"""))
return warehouse_account
+
+def block_negative_stock(allow_negative_stock=False):
+ if cint(frappe.defaults.get_global_default("auto_accounting_for_stock")) and not allow_negative_stock:
+ if cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock")):
+ frappe.throw(_("Negative stock is not allowed in case of Perpetual Inventory, please disable it from Stock Settings"))
+
+def get_valuation_rate(item_code, warehouse, posting_date):
+ last_valuation_rate = frappe.db.sql("""select valuation_rate
+ from `tabStock Ledger Entry`
+ where item_code = %s and warehouse = %s
+ and ifnull(qty_after_transaction, 0) > 0 and posting_date < %s
+ order by posting_date desc limit 1""", (item_code, warehouse, posting_date))
+
+ valuation_rate = flt(last_valuation_rate[0][0]) if last_valuation_rate else 0
+
+ if not valuation_rate:
+ valuation_rate = frappe.db.get_value("Item Price", {"item_code": item_code, "buying": 1}, "price_list_rate")
+
+ return valuation_rate
diff --git a/erpnext/public/js/stock_analytics.js b/erpnext/public/js/stock_analytics.js
index d4f43e9..84c0386 100644
--- a/erpnext/public/js/stock_analytics.js
+++ b/erpnext/public/js/stock_analytics.js
@@ -138,9 +138,17 @@
item.valuation_method : sys_defaults.valuation_method;
var is_fifo = valuation_method == "FIFO";
- var diff = me.get_value_diff(wh, sl, is_fifo);
+ if(sl.voucher_type=="Stock Reconciliation") {
+ var diff = (sl.qty_after_transaction * sl.valuation_rate) - item.closing_qty_value;
+ } else {
+ var diff = me.get_value_diff(wh, sl, is_fifo);
+ }
} else {
- var diff = sl.qty;
+ if(sl.voucher_type=="Stock Reconciliation") {
+ var diff = sl.qty_after_transaction - item.closing_qty_value;
+ } else {
+ var diff = sl.qty;
+ }
}
if(posting_datetime < from_date) {
@@ -150,6 +158,8 @@
} else {
break;
}
+
+ item.closing_qty_value += diff;
}
}
},
diff --git a/erpnext/startup/report_data_map.py b/erpnext/startup/report_data_map.py
index ce71310..81d378c 100644
--- a/erpnext/startup/report_data_map.py
+++ b/erpnext/startup/report_data_map.py
@@ -78,7 +78,8 @@
"Stock Ledger Entry": {
"columns": ["name", "posting_date", "posting_time", "item_code", "warehouse",
"actual_qty as qty", "voucher_type", "voucher_no", "project",
- "ifnull(incoming_rate,0) as incoming_rate", "stock_uom", "serial_no"],
+ "ifnull(incoming_rate,0) as incoming_rate", "stock_uom", "serial_no",
+ "qty_after_transaction", "valuation_rate"],
"order_by": "posting_date, posting_time, name",
"links": {
"item_code": ["Item", "name"],
diff --git a/erpnext/stock/doctype/bin/bin.py b/erpnext/stock/doctype/bin/bin.py
index 3f74c5c..e3269e8 100644
--- a/erpnext/stock/doctype/bin/bin.py
+++ b/erpnext/stock/doctype/bin/bin.py
@@ -11,27 +11,27 @@
def validate(self):
if self.get("__islocal") or not self.stock_uom:
self.stock_uom = frappe.db.get_value('Item', self.item_code, 'stock_uom')
-
+
self.validate_mandatory()
-
+
self.projected_qty = flt(self.actual_qty) + flt(self.ordered_qty) + \
flt(self.indented_qty) + flt(self.planned_qty) - flt(self.reserved_qty)
-
+
def validate_mandatory(self):
qf = ['actual_qty', 'reserved_qty', 'ordered_qty', 'indented_qty']
for f in qf:
- if (not getattr(self, f, None)) or (not self.get(f)):
+ if (not getattr(self, f, None)) or (not self.get(f)):
self.set(f, 0.0)
-
+
def update_stock(self, args):
self.update_qty(args)
-
- if args.get("actual_qty"):
+
+ if args.get("actual_qty") or args.get("voucher_type") == "Stock Reconciliation":
from erpnext.stock.stock_ledger import update_entries_after
-
+
if not args.get("posting_date"):
args["posting_date"] = nowdate()
-
+
# update valuation and qty after transaction for post dated entry
update_entries_after({
"item_code": self.item_code,
@@ -39,21 +39,34 @@
"posting_date": args.get("posting_date"),
"posting_time": args.get("posting_time")
})
-
+
def update_qty(self, args):
# update the stock values (for current quantities)
-
- self.actual_qty = flt(self.actual_qty) + flt(args.get("actual_qty"))
+ if args.get("voucher_type")=="Stock Reconciliation":
+ if args.get('is_cancelled') == 'No':
+ self.actual_qty = args.get("qty_after_transaction")
+ else:
+ qty_after_transaction = frappe.db.get_value("""select qty_after_transaction
+ from `tabStock Ledger Entry`
+ where item_code=%s and warehouse=%s
+ and not (voucher_type='Stock Reconciliation' and voucher_no=%s)
+ order by posting_date desc limit 1""",
+ (self.item_code, self.warehouse, args.get('voucher_no')))
+
+ self.actual_qty = flt(qty_after_transaction[0][0]) if qty_after_transaction else 0.0
+ else:
+ self.actual_qty = flt(self.actual_qty) + flt(args.get("actual_qty"))
+
self.ordered_qty = flt(self.ordered_qty) + flt(args.get("ordered_qty"))
self.reserved_qty = flt(self.reserved_qty) + flt(args.get("reserved_qty"))
self.indented_qty = flt(self.indented_qty) + flt(args.get("indented_qty"))
self.planned_qty = flt(self.planned_qty) + flt(args.get("planned_qty"))
-
+
self.projected_qty = flt(self.actual_qty) + flt(self.ordered_qty) + \
flt(self.indented_qty) + flt(self.planned_qty) - flt(self.reserved_qty)
-
+
self.save()
-
+
def get_first_sle(self):
sle = frappe.db.sql("""
select * from `tabStock Ledger Entry`
@@ -62,4 +75,4 @@
order by timestamp(posting_date, posting_time) asc, name asc
limit 1
""", (self.item_code, self.warehouse), as_dict=1)
- return sle and sle[0] or None
\ No newline at end of file
+ return sle and sle[0] or None
diff --git a/erpnext/stock/doctype/landed_cost_voucher/landed_cost_voucher.py b/erpnext/stock/doctype/landed_cost_voucher/landed_cost_voucher.py
index b4fa971..3046c5e 100644
--- a/erpnext/stock/doctype/landed_cost_voucher/landed_cost_voucher.py
+++ b/erpnext/stock/doctype/landed_cost_voucher/landed_cost_voucher.py
@@ -97,10 +97,10 @@
# update stock & gl entries for cancelled state of PR
pr.docstatus = 2
- pr.update_stock()
+ pr.update_stock_ledger()
pr.make_gl_entries_on_cancel()
# update stock & gl entries for submit state of PR
pr.docstatus = 1
- pr.update_stock()
+ pr.update_stock_ledger()
pr.make_gl_entries()
diff --git a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
index a7fa6bb..fc35222 100644
--- a/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
+++ b/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py
@@ -130,7 +130,7 @@
if not d.prevdoc_docname:
frappe.throw(_("Purchase Order number required for Item {0}").format(d.item_code))
- def update_stock(self):
+ def update_stock_ledger(self):
sl_entries = []
stock_items = self.get_stock_items()
@@ -234,7 +234,7 @@
self.update_ordered_qty()
- self.update_stock()
+ self.update_stock_ledger()
from erpnext.stock.doctype.serial_no.serial_no import update_serial_nos_after_submit
update_serial_nos_after_submit(self, "purchase_receipt_details")
@@ -267,7 +267,7 @@
self.update_ordered_qty()
- self.update_stock()
+ self.update_stock_ledger()
self.update_prevdoc_status()
pc_obj.update_last_purchase_rate(self, 0)
@@ -283,8 +283,11 @@
def get_rate(self,arg):
return frappe.get_doc('Purchase Common').get_rate(arg,self)
- def get_gl_entries(self, warehouse_account=None):
+ def get_gl_entries(self, warehouse_account=None, allow_negative_stock=False):
from erpnext.accounts.general_ledger import process_gl_map
+ from erpnext.controllers.stock_controller import block_negative_stock
+
+ block_negative_stock(allow_negative_stock)
stock_rbnb = self.get_company_default("stock_received_but_not_billed")
expenses_included_in_valuation = self.get_company_default("expenses_included_in_valuation")
diff --git a/erpnext/stock/doctype/stock_entry/stock_entry.py b/erpnext/stock/doctype/stock_entry/stock_entry.py
index 4f3480c..4cc96bf 100644
--- a/erpnext/stock/doctype/stock_entry/stock_entry.py
+++ b/erpnext/stock/doctype/stock_entry/stock_entry.py
@@ -527,7 +527,7 @@
}
}, bom_no=self.bom_no)
- self.get_stock_and_rate()
+ self.e()
def get_bom_raw_materials(self, qty):
from erpnext.manufacturing.doctype.bom.bom import get_bom_items_as_dict
diff --git a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
index 0e92b5d..7fdd440 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
+++ b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
@@ -44,11 +44,14 @@
formatdate(self.posting_date), self.posting_time))
def validate_mandatory(self):
- mandatory = ['warehouse','posting_date','voucher_type','voucher_no','actual_qty','company']
+ mandatory = ['warehouse','posting_date','voucher_type','voucher_no','company']
for k in mandatory:
if not self.get(k):
frappe.throw(_("{0} is required").format(self.meta.get_label(k)))
+ if self.voucher_type != "Stock Reconciliation" and not self.actual_qty:
+ frappe.throw(_("Actual Qty is mandatory"))
+
def validate_item(self):
item_det = frappe.db.sql("""select name, has_batch_no, docstatus, is_stock_item
from tabItem where name=%s""", self.item_code, as_dict=True)[0]
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.json b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.json
index daba967..8434f60 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.json
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.json
@@ -1,5 +1,5 @@
{
- "allow_copy": 1,
+ "allow_copy": 1,
"autoname": "SR/.######",
"creation": "2013-03-28 10:35:31",
"description": "This tool helps you to update or fix the quantity and valuation of stock in the system. It is typically used to synchronise the system values and what actually exists in your warehouses.",
@@ -7,6 +7,7 @@
"doctype": "DocType",
"fields": [
{
+ "default": "Today",
"fieldname": "posting_date",
"fieldtype": "Date",
"in_filter": 0,
@@ -118,7 +119,7 @@
"idx": 1,
"is_submittable": 1,
"max_attachments": 1,
- "modified": "2014-05-26 03:05:54.024413",
+ "modified": "2014-10-07 12:43:52.825575",
"modified_by": "Administrator",
"module": "Stock",
"name": "Stock Reconciliation",
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
index 40a980d..6c3c395 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -22,7 +22,7 @@
self.validate_expense_account()
def on_submit(self):
- self.insert_stock_ledger_entries()
+ self.update_stock_ledger()
self.make_gl_entries()
def on_cancel(self):
@@ -126,10 +126,9 @@
except Exception, e:
self.validation_messages.append(_("Row # ") + ("%d: " % (row_num)) + cstr(e))
- def insert_stock_ledger_entries(self):
+ def update_stock_ledger(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"]
@@ -141,105 +140,27 @@
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 not in ["", None] 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))
+ 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
+ })
- change_in_qty = row.qty not in ["", None] and \
- (flt(row.qty) - flt(previous_sle.get("qty_after_transaction")))
+ if row.qty in ("", None):
+ row.qty = previous_sle.get("qty_after_transaction")
- change_in_rate = row.valuation_rate not in ["", None] and \
- (flt(row.valuation_rate) - flt(previous_sle.get("valuation_rate")))
+ if row.valuation_rate in ("", None):
+ row.valuation_rate = 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)
+ # if row.qty and not row.valuation_rate:
+ # frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code))
- else:
- self.sle_for_fifo(row, previous_sle, change_in_qty, change_in_rate)
+ self.insert_entries(row)
- def sle_for_moving_avg(self, row, previous_sle, change_in_qty, change_in_rate):
- """Insert Stock Ledger Entries for Moving Average valuation"""
- def _get_incoming_rate(qty, valuation_rate, previous_qty, previous_valuation_rate):
- if previous_valuation_rate == 0:
- return flt(valuation_rate)
- else:
- if valuation_rate in ["", None]:
- valuation_rate = previous_valuation_rate
- return (qty * valuation_rate - previous_qty * previous_valuation_rate) \
- / flt(qty - previous_qty)
-
- if change_in_qty:
- # if change in qty, irrespective of change in rate
- incoming_rate = _get_incoming_rate(flt(row.qty), flt(row.valuation_rate),
- flt(previous_sle.get("qty_after_transaction")), flt(previous_sle.get("valuation_rate")))
-
- row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Actual Entry"
- self.insert_entries({"actual_qty": change_in_qty, "incoming_rate": incoming_rate}, row)
-
- elif change_in_rate and flt(previous_sle.get("qty_after_transaction")) > 0:
- # if no change in qty, but change in rate
- # and positive actual stock before this reconciliation
- incoming_rate = _get_incoming_rate(
- flt(previous_sle.get("qty_after_transaction"))+1, flt(row.valuation_rate),
- flt(previous_sle.get("qty_after_transaction")),
- flt(previous_sle.get("valuation_rate")))
-
- # +1 entry
- row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Valuation Adjustment +1"
- self.insert_entries({"actual_qty": 1, "incoming_rate": incoming_rate}, row)
-
- # -1 entry
- row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Valuation Adjustment -1"
- self.insert_entries({"actual_qty": -1}, row)
-
- def sle_for_fifo(self, row, previous_sle, change_in_qty, change_in_rate):
- """Insert Stock Ledger Entries for FIFO valuation"""
- previous_stock_queue = json.loads(previous_sle.get("stock_queue") or "[]")
- previous_stock_qty = sum((batch[0] for batch in previous_stock_queue))
- previous_stock_value = sum((batch[0] * batch[1] for batch in \
- previous_stock_queue))
-
- def _insert_entries():
- if previous_stock_queue != [[row.qty, row.valuation_rate]]:
- # make entry as per attachment
- if flt(row.qty):
- row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Actual Entry"
- self.insert_entries({"actual_qty": row.qty,
- "incoming_rate": flt(row.valuation_rate)}, row)
-
- # Make reverse entry
- if previous_stock_qty:
- row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Reverse Entry"
- self.insert_entries({"actual_qty": -1 * previous_stock_qty,
- "incoming_rate": previous_stock_qty < 0 and
- flt(row.valuation_rate) or 0}, row)
-
-
- if change_in_qty:
- if row.valuation_rate in ["", None]:
- # dont want change in valuation
- if previous_stock_qty > 0:
- # set valuation_rate as previous valuation_rate
- row.valuation_rate = previous_stock_value / flt(previous_stock_qty)
-
- _insert_entries()
-
- elif change_in_rate and previous_stock_qty > 0:
- # if no change in qty, but change in rate
- # and positive actual stock before this reconciliation
-
- row.qty = previous_stock_qty
- _insert_entries()
-
- def insert_entries(self, opts, row):
+ def insert_entries(self, row):
"""Insert Stock Ledger Entries"""
args = frappe._dict({
"doctype": "Stock Ledger Entry",
@@ -251,11 +172,11 @@
"voucher_no": self.name,
"company": self.company,
"stock_uom": frappe.db.get_value("Item", row.item_code, "stock_uom"),
- "voucher_detail_no": row.voucher_detail_no,
"fiscal_year": self.fiscal_year,
- "is_cancelled": "No"
+ "is_cancelled": "No",
+ "qty_after_transaction": row.qty,
+ "valuation_rate": row.valuation_rate
})
- args.update(opts)
self.make_sl_entries([args])
# append to entries
@@ -282,12 +203,12 @@
"posting_time": self.posting_time
})
- def get_gl_entries(self, warehouse_account=None):
+ def get_gl_entries(self, warehouse_account=None, allow_negative_stock=False):
if not self.cost_center:
msgprint(_("Please enter Cost Center"), raise_exception=1)
return super(StockReconciliation, self).get_gl_entries(warehouse_account,
- self.expense_account, self.cost_center)
+ self.expense_account, self.cost_center, allow_negative_stock=allow_negative_stock)
def validate_expense_account(self):
if not cint(frappe.defaults.get_global_default("auto_accounting_for_stock")):
@@ -295,7 +216,7 @@
if not self.expense_account:
msgprint(_("Please enter Expense Account"), raise_exception=1)
- elif not frappe.db.sql("""select * from `tabStock Ledger Entry`"""):
+ elif not frappe.db.sql("""select name from `tabStock Ledger Entry` limit 1"""):
if frappe.db.get_value("Account", self.expense_account, "report_type") == "Profit and Loss":
frappe.throw(_("Difference Account must be a 'Liability' type account, since this Stock Reconciliation is an Opening Entry"))
diff --git a/erpnext/stock/doctype/stock_settings/stock_settings.py b/erpnext/stock/doctype/stock_settings/stock_settings.py
index b505394..95ace86 100644
--- a/erpnext/stock/doctype/stock_settings/stock_settings.py
+++ b/erpnext/stock/doctype/stock_settings/stock_settings.py
@@ -6,18 +6,20 @@
from __future__ import unicode_literals
import frappe
from frappe import _
-
+from frappe.utils import cint
from frappe.model.document import Document
class StockSettings(Document):
def validate(self):
- for key in ["item_naming_by", "item_group", "stock_uom",
- "allow_negative_stock"]:
+ if cint(self.allow_negative_stock) and cint(frappe.defaults.get_global_default("auto_accounting_for_stock")):
+ frappe.throw(_("Negative stock is not allowed in case of Perpetual Inventory"))
+
+ for key in ["item_naming_by", "item_group", "stock_uom", "allow_negative_stock"]:
frappe.db.set_default(key, self.get(key, ""))
-
+
from erpnext.setup.doctype.naming_series.naming_series import set_by_naming_series
- set_by_naming_series("Item", "item_code",
+ set_by_naming_series("Item", "item_code",
self.get("item_naming_by")=="Naming Series", hide_name_field=True)
stock_frozen_limit = 356
@@ -25,3 +27,5 @@
if submitted_stock_frozen > stock_frozen_limit:
self.stock_frozen_upto_days = stock_frozen_limit
frappe.msgprint (_("`Freeze Stocks Older Than` should be smaller than %d days.") %stock_frozen_limit)
+
+
diff --git a/erpnext/stock/page/stock_balance/stock_balance.js b/erpnext/stock/page/stock_balance/stock_balance.js
index 1083414..7405227 100644
--- a/erpnext/stock/page/stock_balance/stock_balance.js
+++ b/erpnext/stock/page/stock_balance/stock_balance.js
@@ -104,8 +104,15 @@
item.valuation_method : sys_defaults.valuation_method;
var is_fifo = valuation_method == "FIFO";
- var qty_diff = sl.qty;
- var value_diff = me.get_value_diff(wh, sl, is_fifo);
+ if(sl.voucher_type=="Stock Reconciliation") {
+ var qty_diff = sl.qty_after_transaction - (item.temp_closing_qty || 0.0);
+ var value_diff = (sl.valuation_rate * sl.qty_after_transaction) - (item.temp_closing_value || 0.0);
+ } else {
+ var qty_diff = sl.qty;
+ var value_diff = me.get_value_diff(wh, sl, is_fifo);
+ }
+ item.temp_closing_qty += qty_diff;
+ item.temp_closing_value += value_diff;
if(sl_posting_date < from_date) {
item.opening_qty += qty_diff;
diff --git a/erpnext/stock/report/stock_ageing/stock_ageing.py b/erpnext/stock/report/stock_ageing/stock_ageing.py
index fc47861..fb5e9f9 100644
--- a/erpnext/stock/report/stock_ageing/stock_ageing.py
+++ b/erpnext/stock/report/stock_ageing/stock_ageing.py
@@ -4,10 +4,10 @@
from __future__ import unicode_literals
import frappe
from frappe import _
-from frappe.utils import date_diff
+from frappe.utils import date_diff, flt
def execute(filters=None):
-
+
columns = get_columns()
item_details = get_fifo_queue(filters)
to_date = filters["to_date"]
@@ -16,35 +16,40 @@
fifo_queue = item_dict["fifo_queue"]
details = item_dict["details"]
if not fifo_queue: continue
-
+
average_age = get_average_age(fifo_queue, to_date)
earliest_age = date_diff(to_date, fifo_queue[0][1])
latest_age = date_diff(to_date, fifo_queue[-1][1])
-
- data.append([item, details.item_name, details.description, details.item_group,
+
+ data.append([item, details.item_name, details.description, details.item_group,
details.brand, average_age, earliest_age, latest_age, details.stock_uom])
-
+
return columns, data
-
+
def get_average_age(fifo_queue, to_date):
batch_age = age_qty = total_qty = 0.0
for batch in fifo_queue:
batch_age = date_diff(to_date, batch[1])
age_qty += batch_age * batch[0]
total_qty += batch[0]
-
+
return (age_qty / total_qty) if total_qty else 0.0
-
+
def get_columns():
- return [_("Item Code") + ":Link/Item:100", _("Item Name") + "::100", _("Description") + "::200",
- _("Item Group") + ":Link/Item Group:100", _("Brand") + ":Link/Brand:100", _("Average Age") + ":Float:100",
+ return [_("Item Code") + ":Link/Item:100", _("Item Name") + "::100", _("Description") + "::200",
+ _("Item Group") + ":Link/Item Group:100", _("Brand") + ":Link/Brand:100", _("Average Age") + ":Float:100",
_("Earliest") + ":Int:80", _("Latest") + ":Int:80", _("UOM") + ":Link/UOM:100"]
-
+
def get_fifo_queue(filters):
item_details = {}
+ prev_qty = 0.0
for d in get_stock_ledger_entries(filters):
item_details.setdefault(d.name, {"details": d, "fifo_queue": []})
fifo_queue = item_details[d.name]["fifo_queue"]
+
+ if d.voucher_type == "Stock Reconciliation":
+ d.actual_qty = flt(d.qty_after_transaction) - flt(prev_qty)
+
if d.actual_qty > 0:
fifo_queue.append([d.actual_qty, d.posting_date])
else:
@@ -52,7 +57,7 @@
while qty_to_pop:
batch = fifo_queue[0] if fifo_queue else [0, None]
if 0 < batch[0] <= qty_to_pop:
- # if batch qty > 0
+ # if batch qty > 0
# not enough or exactly same qty in current batch, clear batch
qty_to_pop -= batch[0]
fifo_queue.pop(0)
@@ -61,12 +66,14 @@
batch[0] -= qty_to_pop
qty_to_pop = 0
+ prev_qty = d.qty_after_transaction
+
return item_details
-
+
def get_stock_ledger_entries(filters):
- return frappe.db.sql("""select
- item.name, item.item_name, item_group, brand, description, item.stock_uom,
- actual_qty, posting_date
+ return frappe.db.sql("""select
+ item.name, item.item_name, item_group, brand, description, item.stock_uom,
+ actual_qty, posting_date, voucher_type, qty_after_transaction
from `tabStock Ledger Entry` sle,
(select name, item_name, description, stock_uom, brand, item_group
from `tabItem` {item_conditions}) item
@@ -77,19 +84,19 @@
order by posting_date, posting_time, sle.name"""\
.format(item_conditions=get_item_conditions(filters),
sle_conditions=get_sle_conditions(filters)), filters, as_dict=True)
-
+
def get_item_conditions(filters):
conditions = []
if filters.get("item_code"):
conditions.append("item_code=%(item_code)s")
if filters.get("brand"):
conditions.append("brand=%(brand)s")
-
+
return "where {}".format(" and ".join(conditions)) if conditions else ""
-
+
def get_sle_conditions(filters):
conditions = []
if filters.get("warehouse"):
conditions.append("warehouse=%(warehouse)s")
-
- return "and {}".format(" and ".join(conditions)) if conditions else ""
\ No newline at end of file
+
+ return "and {}".format(" and ".join(conditions)) if conditions else ""
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index 4c5458d..44f32c9 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -13,16 +13,13 @@
data = []
for sle in sl_entries:
item_detail = item_details[sle.item_code]
- voucher_link_icon = """<a href="%s"><i class="icon icon-share"
- style="cursor: pointer;"></i></a>""" \
- % ("/".join(["#Form", sle.voucher_type, sle.voucher_no]),)
data.append([sle.date, sle.item_code, item_detail.item_name, item_detail.item_group,
item_detail.brand, item_detail.description, sle.warehouse,
item_detail.stock_uom, sle.actual_qty, sle.qty_after_transaction,
(sle.incoming_rate if sle.actual_qty > 0 else 0.0),
sle.valuation_rate, sle.stock_value, sle.voucher_type, sle.voucher_no,
- voucher_link_icon, sle.batch_no, sle.serial_no, sle.company])
+ sle.batch_no, sle.serial_no, sle.company])
return columns, data
@@ -31,7 +28,7 @@
_("Brand") + ":Link/Brand:100", _("Description") + "::200", _("Warehouse") + ":Link/Warehouse:100",
_("Stock UOM") + ":Link/UOM:100", _("Qty") + ":Float:50", _("Balance Qty") + ":Float:100",
_("Incoming Rate") + ":Currency:110", _("Valuation Rate") + ":Currency:110", _("Balance Value") + ":Currency:110",
- _("Voucher Type") + "::110", _("Voucher #") + "::100", _("Link") + "::30", _("Batch") + ":Link/Batch:100",
+ _("Voucher Type") + "::110", _("Voucher #") + ":Dynamic Link/Voucher Type:100", _("Batch") + ":Link/Batch:100",
_("Serial #") + ":Link/Serial No:100", _("Company") + ":Link/Company:100"]
def get_stock_ledger_entries(filters):
diff --git a/erpnext/stock/stock_ledger.py b/erpnext/stock/stock_ledger.py
index 3717bf1..e8a84c2 100644
--- a/erpnext/stock/stock_ledger.py
+++ b/erpnext/stock/stock_ledger.py
@@ -27,7 +27,7 @@
if sle.get('is_cancelled') == 'Yes':
sle['actual_qty'] = -flt(sle['actual_qty'])
- if sle.get("actual_qty"):
+ if sle.get("actual_qty") or sle.voucher_type=="Stock Reconciliation":
sle_id = make_entry(sle)
args = sle.copy()
@@ -36,9 +36,9 @@
"is_amended": is_amended
})
update_bin(args)
+
if cancel:
- delete_cancelled_entry(sl_entries[0].get('voucher_type'),
- sl_entries[0].get('voucher_no'))
+ delete_cancelled_entry(sl_entries[0].get('voucher_type'), sl_entries[0].get('voucher_no'))
def set_as_cancel(voucher_type, voucher_no):
frappe.db.sql("""update `tabStock Ledger Entry` set is_cancelled='Yes',
@@ -83,7 +83,6 @@
entries_to_fix = get_sle_after_datetime(previous_sle or \
{"item_code": args["item_code"], "warehouse": args["warehouse"]}, for_update=True)
-
valuation_method = get_valuation_method(args["item_code"])
stock_value_difference = 0.0
@@ -95,14 +94,23 @@
qty_after_transaction += flt(sle.actual_qty)
continue
+
if sle.serial_no:
valuation_rate = get_serialized_values(qty_after_transaction, sle, valuation_rate)
- elif valuation_method == "Moving Average":
- valuation_rate = get_moving_average_values(qty_after_transaction, sle, valuation_rate)
- else:
- valuation_rate = get_fifo_values(qty_after_transaction, sle, stock_queue)
+ qty_after_transaction += flt(sle.actual_qty)
- qty_after_transaction += flt(sle.actual_qty)
+ else:
+ if sle.voucher_type=="Stock Reconciliation":
+ valuation_rate = sle.valuation_rate
+ qty_after_transaction = sle.qty_after_transaction
+ stock_queue = [[qty_after_transaction, valuation_rate]]
+ else:
+ if valuation_method == "Moving Average":
+ valuation_rate = get_moving_average_values(qty_after_transaction, sle, valuation_rate)
+ else:
+ valuation_rate = get_fifo_values(qty_after_transaction, sle, stock_queue)
+
+ qty_after_transaction += flt(sle.actual_qty)
# get stock value
if sle.serial_no:
diff --git a/erpnext/utilities/repost_stock.py b/erpnext/utilities/repost_stock.py
index 4c14548..9c3bf1d 100644
--- a/erpnext/utilities/repost_stock.py
+++ b/erpnext/utilities/repost_stock.py
@@ -209,3 +209,30 @@
frappe.db.sql("""update `tabSerial No` set warehouse='' where status in ('Delivered', 'Purchase Returned')""")
+def repost_all_stock_vouchers():
+ vouchers = frappe.db.sql("""select distinct voucher_type, voucher_no
+ from `tabStock Ledger Entry` order by posting_date, posting_time, name""")
+
+ rejected = []
+ i = 0
+ for voucher_type, voucher_no in vouchers:
+ i += 1
+ print voucher_type, voucher_no
+ try:
+ for dt in ["Stock Ledger Entry", "GL Entry"]:
+ frappe.db.sql("""delete from `tab%s` where voucher_type=%s and voucher_no=%s"""%
+ (dt, '%s', '%s'), (voucher_type, voucher_no))
+
+ doc = frappe.get_doc(voucher_type, voucher_no)
+ if voucher_type=="Stock Entry" and doc.purpose in ["Manufacture", "Repack"]:
+ doc.get_stock_and_rate(force=1)
+ doc.update_stock_ledger()
+ doc.make_gl_entries()
+ if i%100 == 0:
+ frappe.db.commit()
+ except:
+ rejected.append([voucher_type, voucher_no])
+ pass
+
+ print rejected
+