def execute():
for company in frappe.get_all("Company"):
if not erpbee.is_perpetual_inventory_enabled(company.name):
continue
acc_frozen_upto = frappe.db.get_value(
"Accounts Settings", None, "acc_frozen_upto") or "1900-01-01"
pr_with_rejected_warehouse = frappe.db.sql("""
select pr.name
from `tabPurchase Receipt` pr, `tabPurchase Receipt Item` pr_item
where pr.name = pr_item.parent
and pr.posting_date > %s
and pr.docstatus=1
and pr.company = %s
and pr_item.rejected_qty > 0
""", (acc_frozen_upto, company.name),
as_dict=1)
for d in pr_with_rejected_warehouse:
doc = frappe.get_doc("Purchase Receipt", d.name)
doc.docstatus = 2
doc.make_gl_entries_on_cancel()
# update gl entries for submit state of PR
doc.docstatus = 1
doc.make_gl_entries()
def test_gl_entries_without_perpetual_inventory(self):
frappe.db.set_value("Company", "_Test Company", "round_off_account",
"Round Off - _TC")
pi = frappe.copy_doc(test_records[0])
self.assertTrue(
not cint(erpbee.is_perpetual_inventory_enabled(pi.company)))
pi.insert()
pi.submit()
expected_gl_entries = {
"_Test Payable - _TC": [0, 1512.0],
"_Test Account Cost for Goods Sold - _TC": [1250, 0],
"_Test Account Shipping Charges - _TC": [100, 0],
"_Test Account Excise Duty - _TC": [140, 0],
"_Test Account Education Cess - _TC": [2.8, 0],
"_Test Account S&H Education Cess - _TC": [1.4, 0],
"_Test Account CST - _TC": [29.88, 0],
"_Test Account VAT - _TC": [156.25, 0],
"_Test Account Discount - _TC": [0, 168.03],
"Round Off - _TC": [0, 0.3]
}
gl_entries = frappe.db.sql(
"""select account, debit, credit from `tabGL Entry`
where voucher_type = 'Purchase Invoice' and voucher_no = %s""",
pi.name,
as_dict=1)
for d in gl_entries:
self.assertEqual([d.debit, d.credit],
expected_gl_entries.get(d.account))
def validate_expense_account(self):
if not cint(erpbee.is_perpetual_inventory_enabled(self.company)):
return
if not self.expense_account:
frappe.throw(_("Please enter Expense Account"))
elif self.purpose == "Opening Stock" or 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 Asset/Liability type account, since this Stock Reconciliation is an Opening Entry"), OpeningEntryAccountError)
def execute(filters=None):
if not erpbee.is_perpetual_inventory_enabled(filters.company):
frappe.throw(
_("Perpetual inventory required for the company {0} to view this report."
).format(filters.company))
data = get_data(filters)
columns = get_columns(filters)
return columns, data
def test_gl_entries_with_perpetual_inventory(self):
pi = make_purchase_invoice(
company="_Test Company with perpetual inventory",
warehouse="Stores - TCP1",
cost_center="Main - TCP1",
expense_account="_Test Account Cost for Goods Sold - TCP1",
get_taxes_and_charges=True,
qty=10)
self.assertTrue(
cint(erpbee.is_perpetual_inventory_enabled(pi.company)), 1)
self.check_gle_for_pi(pi.name)
def repost_gl_entries(doc):
if not cint(erpbee.is_perpetual_inventory_enabled(doc.company)):
return
if doc.based_on == 'Transaction':
ref_doc = frappe.get_doc(doc.voucher_type, doc.voucher_no)
items, warehouses = ref_doc.get_items_and_warehouses()
else:
items = [doc.item_code]
warehouses = [doc.warehouse]
update_gl_entries_after(doc.posting_date,
doc.posting_time,
warehouses,
items,
company=doc.company)
def make_gl_entries(self, gl_entries=None, from_repost=False): if self.docstatus == 2: make_reverse_gl_entries(voucher_type=self.doctype, voucher_no=self.name) if cint(erpbee.is_perpetual_inventory_enabled(self.company)): warehouse_account = get_warehouse_account_map(self.company) if self.docstatus==1: if not gl_entries: gl_entries = self.get_gl_entries(warehouse_account) make_gl_entries(gl_entries, from_repost=from_repost) elif self.doctype in ['Purchase Receipt', 'Purchase Invoice'] and self.docstatus == 1: gl_entries = [] gl_entries = self.get_asset_gl_entry(gl_entries) make_gl_entries(gl_entries, from_repost=from_repost)
def check_if_stock_and_account_balance_synced(posting_date,
company,
voucher_type=None,
voucher_no=None):
if not cint(erpbee.is_perpetual_inventory_enabled(company)):
return
accounts = get_stock_accounts(company, voucher_type, voucher_no)
stock_adjustment_account = frappe.db.get_value("Company", company,
"stock_adjustment_account")
for account in accounts:
account_bal, stock_bal, warehouse_list = get_stock_and_account_balance(
account, posting_date, company)
if abs(account_bal - stock_bal) > 0.1:
precision = get_field_precision(
frappe.get_meta("GL Entry").get_field("debit"),
currency=frappe.get_cached_value('Company', company,
"default_currency"))
diff = flt(stock_bal - account_bal, precision)
error_reason = _(
"Stock Value ({0}) and Account Balance ({1}) are out of sync for account {2} and it's linked warehouses as on {3}."
).format(stock_bal, account_bal, frappe.bold(account),
posting_date)
error_resolution = _("Please create an adjustment Journal Entry for amount {0} on {1}")\
.format(frappe.bold(diff), frappe.bold(posting_date))
frappe.msgprint(
msg="""{0}<br></br>{1}<br></br>""".format(
error_reason, error_resolution),
raise_exception=StockValueAndAccountBalanceOutOfSync,
title=_('Values Out Of Sync'),
primary_action={
'label':
_('Make Journal Entry'),
'client_action':
'erpbee.route_to_adjustment_jv',
'args':
get_journal_entry(account, stock_adjustment_account, diff)
})
def execute():
frappe.reload_doctype("Account")
warehouses = frappe.db.sql("""select name, company from tabAccount
where account_type = 'Stock' and is_group = 0
and (warehouse is null or warehouse = '')""",
as_dict=1)
warehouses = [
d.name for d in warehouses
if erpbee.is_perpetual_inventory_enabled(d.company)
]
if len(warehouses) > 0:
warehouses = set_warehouse_for_stock_account(warehouses)
if not warehouses:
return
stock_vouchers = frappe.db.sql(
"""select distinct sle.voucher_type, sle.voucher_no
from `tabStock Ledger Entry` sle
where sle.warehouse in (%s) and creation > '2016-05-01'
and not exists(select name from `tabGL Entry`
where account=sle.warehouse and voucher_type=sle.voucher_type and voucher_no=sle.voucher_no)
order by sle.posting_date""" % ', '.join(['%s'] * len(warehouses)),
tuple(warehouses))
rejected = []
for voucher_type, voucher_no in stock_vouchers:
try:
frappe.db.sql(
"""delete from `tabGL Entry`
where voucher_type=%s and voucher_no=%s""", (voucher_type, voucher_no))
voucher = frappe.get_doc(voucher_type, voucher_no)
voucher.make_gl_entries()
frappe.db.commit()
except Exception as e:
print(frappe.get_traceback())
rejected.append([voucher_type, voucher_no])
frappe.db.rollback()
print(rejected)
def validate_parent_account_for_warehouse(company=None):
if not company:
return
if cint(erpbee.is_perpetual_inventory_enabled(company.name)):
parent_account = frappe.db.sql(
"""select name from tabAccount
where account_type='Stock' and company=%s and is_group=1
and (warehouse is null or warehouse = '')""", company.name)
if not parent_account:
current_parent_accounts_for_warehouse = frappe.db.sql(
"""select parent_account from tabAccount
where account_type='Warehouse' and (warehouse is not null or warehouse != '') """
)
if current_parent_accounts_for_warehouse:
frappe.db.set_value(
"Account", current_parent_accounts_for_warehouse[0][0],
"account_type", "Stock")
def make_warehouse_nestedset(company=None):
validate_parent_account_for_warehouse(company)
stock_account_group = get_stock_account_group(company.name)
enable_perpetual_inventory = cint(
erpbee.is_perpetual_inventory_enabled(company.name)) or 0
if not stock_account_group and enable_perpetual_inventory:
return
if company:
warehouse_group = "{0} - {1}".format(_("All Warehouses"), company.abbr)
ignore_mandatory = False
else:
warehouse_group = _("All Warehouses")
ignore_mandatory = True
if not frappe.db.get_value("Warehouse", warehouse_group):
create_default_warehouse_group(company, stock_account_group,
ignore_mandatory)
set_parent_to_warehouse(warehouse_group, company)
if enable_perpetual_inventory:
set_parent_to_warehouse_account(company)
def get_valuation_rate(item_code,
warehouse,
voucher_type,
voucher_no,
allow_zero_rate=False,
currency=None,
company=None,
raise_error_if_no_rate=True):
# Get valuation rate from last sle for the same item and warehouse
if not company:
company = erpbee.get_default_company()
last_valuation_rate = frappe.db.sql(
"""select valuation_rate
from `tabStock Ledger Entry`
where
item_code = %s
AND warehouse = %s
AND valuation_rate >= 0
AND NOT (voucher_no = %s AND voucher_type = %s)
order by posting_date desc, posting_time desc, name desc limit 1""",
(item_code, warehouse, voucher_no, voucher_type))
if not last_valuation_rate:
# Get valuation rate from last sle for the item against any warehouse
last_valuation_rate = frappe.db.sql(
"""select valuation_rate
from `tabStock Ledger Entry`
where
item_code = %s
AND valuation_rate > 0
AND NOT(voucher_no = %s AND voucher_type = %s)
order by posting_date desc, posting_time desc, name desc limit 1""",
(item_code, voucher_no, voucher_type))
if last_valuation_rate:
return flt(last_valuation_rate[0][0])
# If negative stock allowed, and item delivered without any incoming entry,
# system does not found any SLE, then take valuation rate from Item
valuation_rate = frappe.db.get_value("Item", item_code, "valuation_rate")
if not valuation_rate:
# try Item Standard rate
valuation_rate = frappe.db.get_value("Item", item_code,
"standard_rate")
if not valuation_rate:
# try in price list
valuation_rate = frappe.db.get_value(
'Item Price',
dict(item_code=item_code, buying=1, currency=currency),
'price_list_rate')
if not allow_zero_rate and not valuation_rate and raise_error_if_no_rate \
and cint(erpbee.is_perpetual_inventory_enabled(company)):
frappe.local.message_log = []
form_link = frappe.utils.get_link_to_form("Item", item_code)
message = _(
"Valuation Rate for the Item {0}, is required to do accounting entries for {1} {2}."
).format(form_link, voucher_type, voucher_no)
message += "<br><br>" + _(" Here are the options to proceed:")
solutions = "<li>" + _(
"If the item is transacting as a Zero Valuation Rate item in this entry, please enable 'Allow Zero Valuation Rate' in the {0} Item table."
).format(voucher_type) + "</li>"
solutions += "<li>" + _("If not, you can Cancel / Submit this entry "
) + _("{0}").format(frappe.bold("after")) + _(
" performing either one below:") + "</li>"
sub_solutions = "<ul><li>" + _(
"Create an incoming stock transaction for the Item.") + "</li>"
sub_solutions += "<li>" + _(
"Mention Valuation Rate in the Item master.") + "</li></ul>"
msg = message + solutions + sub_solutions + "</li>"
frappe.throw(msg=msg, title=_("Valuation Rate Missing"))
return valuation_rate