def test_purchase_receipt_gl_entry(self):
pr = make_purchase_receipt(company="_Test Company with perpetual inventory",
warehouse = "Stores - TCP1", supplier_warehouse = "Work in Progress - TCP1",
get_multiple_items = True, get_taxes_and_charges = True)
self.assertEqual(cint(erpnext.is_perpetual_inventory_enabled(pr.company)), 1)
gl_entries = get_gl_entries("Purchase Receipt", pr.name)
self.assertTrue(gl_entries)
stock_in_hand_account = get_inventory_account(pr.company, pr.items[0].warehouse)
fixed_asset_account = get_inventory_account(pr.company, pr.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 - TCP1": [0.0, 500.0],
"_Test Account Shipping Charges - TCP1": [0.0, 100.0],
"_Test Account Customs Duty - TCP1": [0.0, 150.0]
}
else:
expected_values = {
stock_in_hand_account: [375.0, 0.0],
fixed_asset_account: [375.0, 0.0],
"Stock Received But Not Billed - TCP1": [0.0, 500.0],
"_Test Account Shipping Charges - TCP1": [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.assertTrue(get_gl_entries("Purchase Receipt", pr.name))
def test_gl_entries_without_perpetual_inventory(self):
wrapper = frappe.copy_doc(test_records[0])
set_perpetual_inventory(0, wrapper.company)
self.assertTrue(
not cint(erpnext.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 = frappe.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 = frappe.copy_doc(test_records[1])
set_perpetual_inventory(1, pi.company)
self.assertTrue(
cint(erpnext.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 = frappe.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.assertEquals(expected_values[i][0], gle.account)
self.assertEquals(expected_values[i][1], gle.debit)
self.assertEquals(expected_values[i][2], gle.credit)
set_perpetual_inventory(0, pi.company)
def set_expense_account(self, for_validate=False):
auto_accounting_for_stock = erpnext.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()
if self.update_stock:
self.validate_item_code()
self.validate_warehouse()
warehouse_account = get_warehouse_account_map()
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 frappe.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 not item.expense_account and for_validate:
throw(
_("Expense account is mandatory for item {0}").format(
item.item_code or item.item_name))
def get_gl_entries(self, warehouse_account=None):
self.auto_accounting_for_stock = erpnext.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 self.check_asset_cwip_enabled():
self.get_asset_gl_entry(gl_entries)
self.make_tax_gl_entries(gl_entries)
gl_entries = make_regional_gl_entries(gl_entries, self)
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 execute():
for company in frappe.get_all("Company"):
if not erpnext.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(repost_future_gle=False)
# update gl entries for submit state of PR
doc.docstatus = 1
doc.make_gl_entries(repost_future_gle=False)
def validate_account_for_perpetual_inventory(gl_map):
if cint(erpnext.is_perpetual_inventory_enabled(gl_map[0].company)):
account_list = [gl_entries.account for gl_entries in gl_map]
aii_accounts = [
d.name for d in frappe.get_all("Account",
filters={
'account_type': 'Stock',
'is_group': 0,
'company': gl_map[0].company
})
]
for account in account_list:
if account not in aii_accounts:
continue
account_bal, stock_bal, warehouse_list = get_stock_and_account_balance(
account, gl_map[0].posting_date, gl_map[0].company)
if gl_map[0].voucher_type == "Journal Entry":
# In case of Journal Entry, there are no corresponding SL entries,
# hence deducting currency amount
account_bal -= flt(gl_map[0].debit) - flt(gl_map[0].credit)
if account_bal == stock_bal:
frappe.throw(
_("Account: {0} can only be updated via Stock Transactions"
).format(account), StockAccountInvalidTransaction)
def test_gl_entries_with_aia_for_non_stock_items(self):
pi = frappe.copy_doc(test_records[1])
set_perpetual_inventory(1, pi.company)
self.assertTrue(cint(erpnext.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 = frappe.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.assertEquals(expected_values[i][0], gle.account)
self.assertEquals(expected_values[i][1], gle.debit)
self.assertEquals(expected_values[i][2], gle.credit)
set_perpetual_inventory(0, pi.company)
def make_gl_entries(self, gl_entries=None, repost_future_gle=True, from_repost=False): auto_accounting_for_stock = erpnext.is_perpetual_inventory_enabled(self.company) if not self.grand_total: return if not gl_entries: gl_entries = self.get_gl_entries() if gl_entries: from erpnext.accounts.general_ledger import make_gl_entries # if POS and amount is written off, updating outstanding amt after posting all gl entries update_outstanding = "No" if (cint(self.is_pos) or self.write_off_account) else "Yes" make_gl_entries(gl_entries, cancel=(self.docstatus == 2), update_outstanding=update_outstanding, merge_entries=False) if update_outstanding == "No": from erpnext.accounts.doctype.gl_entry.gl_entry import update_outstanding_amt update_outstanding_amt(self.debit_to, "Customer", self.customer, self.doctype, self.return_against if cint(self.is_return) else self.name) if repost_future_gle and cint(self.update_stock) \ and cint(auto_accounting_for_stock): items, warehouses = self.get_items_and_warehouses() update_gl_entries_after(self.posting_date, self.posting_time, warehouses, items) elif self.docstatus == 2 and cint(self.update_stock) \ and cint(auto_accounting_for_stock): from erpnext.accounts.general_ledger import delete_gl_entries delete_gl_entries(voucher_type=self.doctype, voucher_no=self.name)
def make_item_gl_entries(self, gl_entries):
# income account gl entries
for item in self.get("items"):
if flt(item.base_net_amount):
account_currency = get_account_currency(item.income_account)
gl_entries.append(
self.get_gl_dict({
"account": item.income_account,
"against": self.customer,
"credit": item.base_net_amount,
"credit_in_account_currency": item.base_net_amount \
if account_currency==self.company_currency else item.net_amount,
"cost_center": item.cost_center
}, account_currency)
)
if item.is_fixed_asset:
asset = frappe.get_doc("Asset", item.asset)
fixed_asset_gl_entries = get_gl_entries_on_asset_disposal(asset, is_sale=True)
for gle in fixed_asset_gl_entries:
gle["against"] = self.customer
gl_entries.append(self.get_gl_dict(gle))
asset.db_set("disposal_date", self.posting_date)
asset.set_status("Sold" if self.docstatus==1 else None)
# expense account gl entries
if cint(self.update_stock) and \
erpnext.is_perpetual_inventory_enabled(self.company):
gl_entries += super(SalesInvoice, self).get_gl_entries()
def test_gl_entries_without_perpetual_inventory(self):
frappe.db.set_value("Company", "_Test Company", "round_off_account", "Round Off - _TC")
wrapper = frappe.copy_doc(test_records[0])
set_perpetual_inventory(0, wrapper.company)
self.assertTrue(not cint(erpnext.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 = frappe.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 make_item_gl_entries(self, gl_entries):
# income account gl entries
for item in self.get("items"):
if flt(item.base_net_amount):
if item.is_fixed_asset:
asset = frappe.get_doc("Asset", item.asset)
fixed_asset_gl_entries = get_gl_entries_on_asset_disposal(
asset, item.base_net_amount)
for gle in fixed_asset_gl_entries:
gle["against"] = self.customer
gl_entries.append(self.get_gl_dict(gle))
asset.db_set("disposal_date", self.posting_date)
asset.set_status("Sold" if self.docstatus == 1 else None)
else:
account_currency = get_account_currency(
item.income_account)
gl_entries.append(
self.get_gl_dict({
"account": item.income_account,
"against": self.customer,
"credit": item.base_net_amount,
"credit_in_account_currency": item.base_net_amount \
if account_currency==self.company_currency else item.net_amount,
"cost_center": item.cost_center
}, account_currency)
)
# expense account gl entries
if cint(self.update_stock) and \
erpnext.is_perpetual_inventory_enabled(self.company):
gl_entries += super(SalesInvoice, self).get_gl_entries()
def set_expense_account(self, for_validate=False):
auto_accounting_for_stock = erpnext.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()
if self.update_stock:
self.validate_item_code()
self.validate_warehouse()
warehouse_account = get_warehouse_account_map()
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 frappe.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 not item.expense_account and for_validate:
throw(_("Expense account is mandatory for item {0}").format(item.item_code or item.item_name))
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 = erpnext.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
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 = frappe.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 = 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(erpnext.is_perpetual_inventory_enabled(company)):
frappe.local.message_log = []
frappe.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 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 = erpnext.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(erpnext.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
def validate_expense_account(self):
if not cint(erpnext.is_perpetual_inventory_enabled(self.company)):
return
if not self.expense_account:
msgprint(_("Please enter Expense Account"), raise_exception=1)
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 Asset/Liability type account, since this Stock Reconciliation is an Opening Entry"), OpeningEntryAccountError)
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(erpnext.is_perpetual_inventory_enabled(pi.company)), 1) self.check_gle_for_pi(pi.name)
def validate_expense_account(self):
if not cint(erpnext.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 validate_account_for_perpetual_inventory(gl_map):
if cint(erpnext.is_perpetual_inventory_enabled(gl_map[0].company)) \
and gl_map[0].voucher_type=="Journal Entry":
aii_accounts = [d[0] for d in frappe.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:
frappe.throw(_("Account: {0} can only be updated via Stock Transactions")
.format(entry.account), StockAccountInvalidTransaction)
def test_gl_entries_with_perpetual_inventory(self): pi = frappe.copy_doc(test_records[1]) set_perpetual_inventory(1, pi.company) self.assertTrue(cint(erpnext.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 execute(filters=None):
if not erpnext.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 validate_account_for_perpetual_inventory(gl_map):
if cint(erpnext.is_perpetual_inventory_enabled(gl_map[0].company)) \
and gl_map[0].voucher_type=="Journal Entry":
aii_accounts = [d[0] for d in frappe.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:
frappe.throw(_("Account: {0} can only be updated via Stock Transactions")
.format(entry.account), StockAccountInvalidTransaction)
def repost_gl_entries(doc):
if not cint(erpnext.is_perpetual_inventory_enabled(doc.company)):
return
# directly modified transactions
directly_dependent_transactions = _get_directly_dependent_vouchers(doc)
repost_affected_transaction = get_affected_transactions(doc)
repost_gle_for_stock_vouchers(
directly_dependent_transactions + list(repost_affected_transaction),
doc.posting_date,
doc.company,
)
def set_expense_account(self, for_validate=False):
auto_accounting_for_stock = erpnext.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 opening entry, drop-ship entry and fixed asset items
if item.item_code:
asset_category = frappe.get_cached_value("Item", item.item_code, "asset_category")
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 frappe.db.get_value("Purchase Order Item", item.po_detail, "delivered_by_supplier")):
if self.update_stock and (not item.from_warehouse):
item.expense_account = warehouse_account[item.warehouse]["account"]
else:
# check if 'Stock Received But Not Billed' account is credited in Purchase receipt or not
if item.purchase_receipt:
negative_expense_booked_in_pr = frappe.db.sql("""select name from `tabGL Entry`
where voucher_type='Purchase Receipt' and voucher_no=%s and account = %s""",
(item.purchase_receipt, stock_not_billed_account))
if negative_expense_booked_in_pr:
item.expense_account = stock_not_billed_account
else:
# If no purchase receipt present then book expense in 'Stock Received But Not Billed'
# This is done in cases when Purchase Invoice is created before Purchase Receipt
item.expense_account = stock_not_billed_account
elif item.is_fixed_asset and not is_cwip_accounting_enabled(asset_category):
item.expense_account = get_asset_category_account('fixed_asset_account', item=item.item_code,
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 repost_gl_entries(doc):
if not cint(erpnext.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 validate_account_for_perpetual_inventory(gl_map):
if cint(erpnext.is_perpetual_inventory_enabled(gl_map[0].company)):
account_list = [gl_entries.account for gl_entries in gl_map]
aii_accounts = [d.name for d in frappe.get_all("Account",
filters={'account_type': 'Stock', 'is_group': 0, 'company': gl_map[0].company})]
for account in account_list:
if account not in aii_accounts:
continue
# Always use current date to get stock and account balance as there can future entries for
# other items
account_bal, stock_bal, warehouse_list = get_stock_and_account_balance(account,
getdate(), gl_map[0].company)
if gl_map[0].voucher_type=="Journal Entry":
# In case of Journal Entry, there are no corresponding SL entries,
# hence deducting currency amount
account_bal -= flt(gl_map[0].debit) - flt(gl_map[0].credit)
if account_bal == stock_bal:
frappe.throw(_("Account: {0} can only be updated via Stock Transactions")
.format(account), StockAccountInvalidTransaction)
elif 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', gl_map[0].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.").format(
stock_bal, account_bal, frappe.bold(account))
error_resolution = _("Please create adjustment Journal Entry for amount {0} ").format(frappe.bold(diff))
stock_adjustment_account = frappe.db.get_value("Company",gl_map[0].company,"stock_adjustment_account")
db_or_cr_warehouse_account =('credit_in_account_currency' if diff < 0 else 'debit_in_account_currency')
db_or_cr_stock_adjustment_account = ('debit_in_account_currency' if diff < 0 else 'credit_in_account_currency')
journal_entry_args = {
'accounts':[
{'account': account, db_or_cr_warehouse_account : abs(diff)},
{'account': stock_adjustment_account, db_or_cr_stock_adjustment_account : abs(diff) }]
}
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': 'erpnext.route_to_adjustment_jv',
'args': journal_entry_args
})
def validate_parent_account_for_warehouse(company=None):
if not company:
return
if cint(erpnext.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 test_gl_entries_with_perpetual_inventory_against_pr(self):
pr = frappe.copy_doc(pr_test_records[0])
set_perpetual_inventory(1, pr.company)
self.assertTrue(cint(erpnext.is_perpetual_inventory_enabled(pr.company)), 1)
pr.submit()
pi = frappe.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 test_gl_entries_with_perpetual_inventory_against_pr(self): pr = make_purchase_receipt(company="_Test Company with perpetual inventory", supplier_warehouse="Work In Progress - TCP1", warehouse= "Stores - TCP1", cost_center = "Main - TCP1", get_taxes_and_charges=True,) self.assertTrue(cint(erpnext.is_perpetual_inventory_enabled(pr.company)), 1) pi = make_purchase_invoice(company="_Test Company with perpetual inventory", supplier_warehouse="Work In Progress - TCP1", warehouse= "Stores - TCP1", cost_center = "Main - TCP1", expense_account ="_Test Account Cost for Goods Sold - TCP1", get_taxes_and_charges=True, qty=10,do_not_save= "True") for d in pi.items: d.purchase_receipt = pr.name pi.insert() pi.submit() self.check_gle_for_pi(pi.name)
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(erpnext.is_perpetual_inventory_enabled(self.company)): warehouse_account = get_warehouse_account_map() 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)
def make_gl_entries(self, gl_entries=None): if self.docstatus == 2: make_reverse_gl_entries(voucher_type=self.doctype, voucher_no=self.name) if cint(erpnext.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) 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)
def get_gl_entries(self, warehouse_account):
gl_entries = []
self.auto_accounting_for_stock = erpnext.is_perpetual_inventory_enabled(self.company)
target_warehouse_account = warehouse_account.get(self.warehouse)
if self.auto_accounting_for_stock:
self.get_stock_gl_entries(gl_entries, warehouse_account)
if self.purpose == "Subcontract":
self.make_supplier_gl_entry(gl_entries, target_warehouse_account)
self.make_payment_gl_entries(gl_entries)
self.make_tax_gl_entries(gl_entries)
print(gl_entries)
print("FINAL")
return gl_entries
def make_item_gl_entries(self, gl_entries):
# income account gl entries
for item in self.get("items"):
if flt(item.base_net_amount):
if item.is_fixed_asset:
asset = frappe.get_doc("Asset", item.asset)
fixed_asset_gl_entries = get_gl_entries_on_asset_disposal(
asset, item.base_net_amount)
for gle in fixed_asset_gl_entries:
gle["against"] = self.customer
gl_entries.append(self.get_gl_dict(gle))
asset.db_set("disposal_date", self.posting_date)
asset.set_status("Sold" if self.docstatus == 1 else None)
else:
account_currency = get_account_currency(
item.income_account)
prefix1 = self.return_against if cint(
self.is_return) else self.name
prefix = prefix1[:2]
code_jour = frappe.db.get_value("Series",
prefix,
"code_jour",
order_by="name")
cat_cpt = frappe.db.get_value("Customer",
self.customer,
"num_cc",
order_by="name")
gl_entries.append(
self.get_gl_dict({
"account": item.cession_account if cat_cpt == "Cession" else item.income_account,
"against": self.customer,
"credit": item.base_net_amount,
"credit_in_account_currency": item.base_net_amount \
if account_currency==self.company_currency else item.net_amount,
"code_jour": code_jour,
"cost_center": item.cost_center
}, account_currency)
)
# expense account gl entries
if cint(self.update_stock) and \
erpnext.is_perpetual_inventory_enabled(self.company):
gl_entries += super(SalesInvoice, self).get_gl_entries()
def get_gl_entries(self, warehouse_account=None):
self.auto_accounting_for_stock = erpnext.is_perpetual_inventory_enabled(self.company)
self.stock_received_but_not_billed = self.get_company_default("stock_received_but_not_billed")
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)
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)
return gl_entries
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(erpnext.is_perpetual_inventory_enabled(self.company)):
warehouse_account = get_warehouse_account_map()
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)
def check_if_stock_and_account_balance_synced(posting_date,
company,
voucher_type=None,
voucher_no=None):
if not cint(erpnext.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':
'erpnext.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 erpnext.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 get_valuation_rate(item_code, warehouse, voucher_type, voucher_no,
allow_zero_rate=False, currency=None, company=None):
# Get valuation rate from last sle for the same item and warehouse
if not company:
company = erpnext.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
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 = frappe.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)
valuation_rate = flt(last_valuation_rate[0][0]) if last_valuation_rate else 0
if not valuation_rate:
# If negative stock allowed, and item delivered without any incoming entry,
# syste 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 cint(erpnext.is_perpetual_inventory_enabled(company)):
frappe.local.message_log = []
frappe.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 sample 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 get_gl_entries(self, warehouse_account):
gl_entries = []
self.auto_accounting_for_stock = erpnext.is_perpetual_inventory_enabled(
self.company)
if self.auto_accounting_for_stock:
gl_entries = super(MinFact, self).get_gl_entries(warehouse_account)
self.make_production_item_gl_entry(gl_entries)
if self.purpose == "Subcontract":
self.make_supplier_gl_entry(gl_entries)
self.make_tax_gl_entries(gl_entries)
self.make_payment_gl_entries(gl_entries)
else:
self.make_additional_cost_gl_entry(gl_entries)
print("FINAL")
print(gl_entries)
return gl_entries
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(erpnext.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 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(erpnext.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 or self.flags.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_purchase_receipt_gl_entry(self):
pr = frappe.copy_doc(test_records[0])
set_perpetual_inventory(1, pr.company)
self.assertEqual(cint(erpnext.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.assertEquals(expected_values[gle.account][0], gle.debit)
self.assertEquals(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 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 frappe.db.get_value('Account',
item.expense_account, 'account_type') != 'Asset Received But Not Billed'):
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(erpnext.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
