def execute():
for company in dataent.get_all("Company"):
if not epaas.is_perpetual_inventory_enabled(company.name):
continue
acc_frozen_upto = dataent.db.get_value("Accounts Settings", None, "acc_frozen_upto") or "1900-01-01"
pr_with_rejected_warehouse = dataent.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 = dataent.get_doc("Purchase Receipt", d.name)
doc.docstatus = 2
doc.make_gl_entries_on_cancel(repost_future_gle=False)
# update gl entries for submit state of PR
doc.docstatus = 1
doc.make_gl_entries(repost_future_gle=False)
def make_gl_entries(self,
gl_entries=None,
repost_future_gle=True,
from_repost=False):
if self.docstatus == 2:
delete_gl_entries(voucher_type=self.doctype, voucher_no=self.name)
if cint(epaas.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)
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,
company=self.company)
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 test_gl_entries_without_perpetual_inventory(self):
dataent.db.set_value("Company", "_Test Company", "round_off_account", "Round Off - _TC")
wrapper = dataent.copy_doc(test_records[0])
set_perpetual_inventory(0, wrapper.company)
self.assertTrue(not cint(epaas.is_perpetual_inventory_enabled(wrapper.company)))
wrapper.insert()
wrapper.submit()
wrapper.load_from_db()
dl = wrapper
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 = dataent.db.sql("""select account, debit, credit from `tabGL Entry`
where voucher_type = 'Purchase Invoice' and voucher_no = %s""", dl.name, as_dict=1)
for d in gl_entries:
self.assertEqual([d.debit, d.credit], expected_gl_entries.get(d.account))
def test_gl_entries_with_aia_for_non_stock_items(self):
pi = dataent.copy_doc(test_records[1])
set_perpetual_inventory(1, pi.company)
self.assertTrue(cint(epaas.is_perpetual_inventory_enabled(pi.company)), 1)
pi.get("items")[0].item_code = "_Test Non Stock Item"
pi.get("items")[0].expense_account = "_Test Account Cost for Goods Sold - _TC"
pi.get("taxes").pop(0)
pi.get("taxes").pop(1)
pi.insert()
pi.submit()
gl_entries = dataent.db.sql("""select account, debit, credit
from `tabGL Entry` where voucher_type='Purchase Invoice' and voucher_no=%s
order by account asc""", pi.name, as_dict=1)
self.assertTrue(gl_entries)
expected_values = sorted([
["_Test Payable - _TC", 0, 620],
["_Test Account Cost for Goods Sold - _TC", 500.0, 0],
["_Test Account VAT - _TC", 120.0, 0],
])
for i, gle in enumerate(gl_entries):
self.assertEqual(expected_values[i][0], gle.account)
self.assertEqual(expected_values[i][1], gle.debit)
self.assertEqual(expected_values[i][2], gle.credit)
set_perpetual_inventory(0, pi.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 = epaas.get_default_company()
last_valuation_rate = dataent.db.sql(
"""select valuation_rate
from `tabStock Ledger Entry`
where item_code = %s and warehouse = %s
and valuation_rate >= 0
order by posting_date desc, posting_time desc, name desc limit 1""",
(item_code, warehouse))
if not last_valuation_rate:
# Get valuation rate from last sle for the item against any warehouse
last_valuation_rate = dataent.db.sql(
"""select valuation_rate
from `tabStock Ledger Entry`
where item_code = %s and valuation_rate > 0
order by posting_date desc, posting_time desc, name desc limit 1""",
item_code)
if last_valuation_rate:
return flt(
last_valuation_rate[0]
[0]) # as there is previous records, it might come with zero rate
# 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 = dataent.db.get_value("Item", item_code, "valuation_rate")
if not valuation_rate:
# try Item Standard rate
valuation_rate = dataent.db.get_value("Item", item_code,
"standard_rate")
if not valuation_rate:
# try in price list
valuation_rate = dataent.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(epaas.is_perpetual_inventory_enabled(company)):
dataent.local.message_log = []
dataent.throw(
_("Valuation rate not found for the Item {0}, which is required to do accounting entries for {1} {2}. If the item is transacting as a zero valuation rate item in the {1}, please mention that in the {1} Item table. Otherwise, please create an incoming stock transaction for the item or mention valuation rate in the Item record, and then try submiting/cancelling this entry"
).format(item_code, voucher_type, voucher_no))
return valuation_rate
def test_gl_entries_with_perpetual_inventory(self): pi = dataent.copy_doc(test_records[1]) set_perpetual_inventory(1, pi.company) self.assertTrue(cint(epaas.is_perpetual_inventory_enabled(pi.company)), 1) pi.insert() pi.submit() self.check_gle_for_pi(pi.name) set_perpetual_inventory(0, pi.company)
def validate_expense_account(self):
if not cint(epaas.is_perpetual_inventory_enabled(self.company)):
return
if not self.expense_account:
dataent.throw(_("Please enter Expense Account"))
elif self.purpose == "Opening Stock" or not dataent.db.sql(
"""select name from `tabStock Ledger Entry` limit 1"""):
if dataent.db.get_value("Account", self.expense_account,
"report_type") == "Profit and Loss":
dataent.throw(
_("Difference Account must be a Asset/Liability type account, since this Stock Reconciliation is an Opening Entry"
), OpeningEntryAccountError)
def validate_account_for_perpetual_inventory(gl_map):
if cint(epaas.is_perpetual_inventory_enabled(gl_map[0].company)) \
and gl_map[0].voucher_type=="Journal Entry":
aii_accounts = [
d[0] for d in dataent.db.sql("""select name from tabAccount
where account_type = 'Stock' and is_group=0""")
]
for entry in gl_map:
if entry.account in aii_accounts:
dataent.throw(
_("Account: {0} can only be updated via Stock Transactions"
).format(entry.account), StockAccountInvalidTransaction)
def test_gl_entries_with_perpetual_inventory_against_pr(self):
pr = dataent.copy_doc(pr_test_records[0])
set_perpetual_inventory(1, pr.company)
self.assertTrue(cint(epaas.is_perpetual_inventory_enabled(pr.company)), 1)
pr.submit()
pi = dataent.copy_doc(test_records[1])
for d in pi.get("items"):
d.purchase_receipt = pr.name
pi.insert()
pi.submit()
self.check_gle_for_pi(pi.name)
set_perpetual_inventory(0, pr.company)
def execute():
dataent.reload_doctype("Account")
warehouses = dataent.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 epaas.is_perpetual_inventory_enabled(d.company)
]
if len(warehouses) > 0:
warehouses = set_warehouse_for_stock_account(warehouses)
if not warehouses:
return
stock_vouchers = dataent.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:
dataent.db.sql(
"""delete from `tabGL Entry`
where voucher_type=%s and voucher_no=%s""", (voucher_type, voucher_no))
voucher = dataent.get_doc(voucher_type, voucher_no)
voucher.make_gl_entries()
dataent.db.commit()
except Exception as e:
print(dataent.get_traceback())
rejected.append([voucher_type, voucher_no])
dataent.db.rollback()
print(rejected)
def set_expense_account(self, for_validate=False):
auto_accounting_for_stock = epaas.is_perpetual_inventory_enabled(self.company)
if auto_accounting_for_stock:
stock_not_billed_account = self.get_company_default("stock_received_but_not_billed")
stock_items = self.get_stock_items()
asset_items = [d.is_fixed_asset for d in self.items if d.is_fixed_asset]
if len(asset_items) > 0:
asset_received_but_not_billed = self.get_company_default("asset_received_but_not_billed")
if self.update_stock:
self.validate_item_code()
self.validate_warehouse()
if auto_accounting_for_stock:
warehouse_account = get_warehouse_account_map(self.company)
for item in self.get("items"):
# in case of auto inventory accounting,
# expense account is always "Stock Received But Not Billed" for a stock item
# except epening entry, drop-ship entry and fixed asset items
if auto_accounting_for_stock and item.item_code in stock_items \
and self.is_opening == 'No' and not item.is_fixed_asset \
and (not item.po_detail or
not dataent.db.get_value("Purchase Order Item", item.po_detail, "delivered_by_supplier")):
if self.update_stock:
item.expense_account = warehouse_account[item.warehouse]["account"]
else:
item.expense_account = stock_not_billed_account
elif item.is_fixed_asset and is_cwip_accounting_disabled():
if not item.asset:
dataent.throw(_("Row {0}: asset is required for item {1}")
.format(item.idx, item.item_code))
item.expense_account = get_asset_category_account(item.asset, 'fixed_asset_account',
company = self.company)
elif item.is_fixed_asset and item.pr_detail:
item.expense_account = asset_received_but_not_billed
elif not item.expense_account and for_validate:
throw(_("Expense account is mandatory for item {0}").format(item.item_code or item.item_name))
def validate_parent_account_for_warehouse(company=None):
if not company:
return
if cint(epaas.is_perpetual_inventory_enabled(company.name)):
parent_account = dataent.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 = dataent.db.sql(
"""select parent_account from tabAccount
where account_type='Warehouse' and (warehouse is not null or warehouse != '') """
)
if current_parent_accounts_for_warehouse:
dataent.db.set_value(
"Account", current_parent_accounts_for_warehouse[0][0],
"account_type", "Stock")
def test_purchase_receipt_gl_entry(self):
pr = dataent.copy_doc(test_records[0])
set_perpetual_inventory(1, pr.company)
self.assertEqual(
cint(epaas.is_perpetual_inventory_enabled(pr.company)), 1)
pr.insert()
pr.submit()
gl_entries = get_gl_entries("Purchase Receipt", pr.name)
self.assertTrue(gl_entries)
stock_in_hand_account = get_inventory_account(
pr.company,
pr.get("items")[0].warehouse)
fixed_asset_account = get_inventory_account(
pr.company,
pr.get("items")[1].warehouse)
if stock_in_hand_account == fixed_asset_account:
expected_values = {
stock_in_hand_account: [750.0, 0.0],
"Stock Received But Not Billed - _TC": [0.0, 500.0],
"Expenses Included In Valuation - _TC": [0.0, 250.0]
}
else:
expected_values = {
stock_in_hand_account: [375.0, 0.0],
fixed_asset_account: [375.0, 0.0],
"Stock Received But Not Billed - _TC": [0.0, 500.0],
"Expenses Included In Valuation - _TC": [0.0, 250.0]
}
for gle in gl_entries:
self.assertEqual(expected_values[gle.account][0], gle.debit)
self.assertEqual(expected_values[gle.account][1], gle.credit)
pr.cancel()
self.assertFalse(get_gl_entries("Purchase Receipt", pr.name))
set_perpetual_inventory(0, pr.company)
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(
epaas.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 dataent.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_gl_entries(self, warehouse_account=None):
self.auto_accounting_for_stock = epaas.is_perpetual_inventory_enabled(self.company)
if self.auto_accounting_for_stock:
self.stock_received_but_not_billed = self.get_company_default("stock_received_but_not_billed")
else:
self.stock_received_but_not_billed = None
self.expenses_included_in_valuation = self.get_company_default("expenses_included_in_valuation")
self.negative_expense_to_be_booked = 0.0
gl_entries = []
self.make_supplier_gl_entry(gl_entries)
self.make_item_gl_entries(gl_entries)
if not is_cwip_accounting_disabled():
self.get_asset_gl_entry(gl_entries)
self.make_tax_gl_entries(gl_entries)
gl_entries = merge_similar_entries(gl_entries)
self.make_payment_gl_entries(gl_entries)
self.make_write_off_gl_entry(gl_entries)
self.make_gle_for_rounding_adjustment(gl_entries)
return gl_entries
def get_asset_gl_entry(self, gl_entries):
for item in self.get("items"):
if item.is_fixed_asset:
eiiav_account = self.get_company_default("expenses_included_in_asset_valuation")
asset_amount = flt(item.net_amount) + flt(item.item_tax_amount/self.conversion_rate)
base_asset_amount = flt(item.base_net_amount + item.item_tax_amount)
if (not item.expense_account or dataent.db.get_value('Account',
item.expense_account, 'account_type') not in ['Asset Received But Not Billed', 'Fixed Asset']):
arbnb_account = self.get_company_default("asset_received_but_not_billed")
item.expense_account = arbnb_account
if not self.update_stock:
asset_rbnb_currency = get_account_currency(item.expense_account)
gl_entries.append(self.get_gl_dict({
"account": item.expense_account,
"against": self.supplier,
"remarks": self.get("remarks") or _("Accounting Entry for Asset"),
"debit": base_asset_amount,
"debit_in_account_currency": (base_asset_amount
if asset_rbnb_currency == self.company_currency else asset_amount),
"cost_center": item.cost_center
}))
if item.item_tax_amount:
asset_eiiav_currency = get_account_currency(eiiav_account)
gl_entries.append(self.get_gl_dict({
"account": eiiav_account,
"against": self.supplier,
"remarks": self.get("remarks") or _("Accounting Entry for Asset"),
"cost_center": item.cost_center,
"credit": item.item_tax_amount,
"credit_in_account_currency": (item.item_tax_amount
if asset_eiiav_currency == self.company_currency else
item.item_tax_amount / self.conversion_rate)
}))
else:
cwip_account = get_asset_account("capital_work_in_progress_account",
item.asset, company = self.company)
cwip_account_currency = get_account_currency(cwip_account)
gl_entries.append(self.get_gl_dict({
"account": cwip_account,
"against": self.supplier,
"remarks": self.get("remarks") or _("Accounting Entry for Asset"),
"debit": base_asset_amount,
"debit_in_account_currency": (base_asset_amount
if cwip_account_currency == self.company_currency else asset_amount),
"cost_center": self.cost_center
}))
if item.item_tax_amount and not cint(epaas.is_perpetual_inventory_enabled(self.company)):
asset_eiiav_currency = get_account_currency(eiiav_account)
gl_entries.append(self.get_gl_dict({
"account": eiiav_account,
"against": self.supplier,
"remarks": self.get("remarks") or _("Accounting Entry for Asset"),
"cost_center": item.cost_center,
"credit": item.item_tax_amount,
"credit_in_account_currency": (item.item_tax_amount
if asset_eiiav_currency == self.company_currency else
item.item_tax_amount / self.conversion_rate)
}))
return gl_entries