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