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