def get_stock_qty(item_code, warehouse):
	return get_previous_sle({
		"item_code": item_code,
		"warehouse": warehouse,
		"posting_date": nowdate(),
		"posting_time": nowtime()
	}).get("qty_after_transaction") or 0
Exemple #2
0
def get_incoming_rate(args):
	"""Get Incoming Rate based on valuation method"""
	from erpnext.stock.stock_ledger import get_previous_sle, get_valuation_rate
	if isinstance(args, basestring):
		args = json.loads(args)

	in_rate = 0
	if (args.get("serial_no") or "").strip():
		in_rate = get_avg_purchase_rate(args.get("serial_no"))
	else:
		valuation_method = get_valuation_method(args.get("item_code"))
		previous_sle = get_previous_sle(args)
		if valuation_method == 'FIFO':
			if previous_sle:
				previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]')
				in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0
		elif valuation_method == 'Moving Average':
			in_rate = previous_sle.get('valuation_rate') or 0

	if not in_rate:
		voucher_no = args.get('voucher_no') or args.get('name')
		in_rate = get_valuation_rate(args.get('item_code'), args.get('warehouse'),
			args.get('voucher_type'), voucher_no, args.get('allow_zero_valuation'),
			currency=erpnext.get_company_currency(args.get('company')), company=args.get('company'))

	return in_rate
	def update_stock_ledger(self):
		"""	find difference between current and expected entries
			and create stock ledger entries based on the difference"""
		from erpnext.stock.stock_ledger import get_previous_sle

		row_template = ["item_code", "warehouse", "qty", "valuation_rate"]

		if not self.reconciliation_json:
			msgprint(_("""Stock Reconciliation file not uploaded"""), raise_exception=1)

		data = json.loads(self.reconciliation_json)
		for row_num, row in enumerate(data[data.index(self.head_row)+1:]):
			row = frappe._dict(zip(row_template, row))
			row["row_num"] = row_num

			if row.qty in ("", None) or row.valuation_rate in ("", None):
				previous_sle = get_previous_sle({
					"item_code": row.item_code,
					"warehouse": row.warehouse,
					"posting_date": self.posting_date,
					"posting_time": self.posting_time
				})

				if row.qty in ("", None):
					row.qty = previous_sle.get("qty_after_transaction")

				if row.valuation_rate in ("", None):
					row.valuation_rate = previous_sle.get("valuation_rate")

			# if row.qty and not row.valuation_rate:
			# 	frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code))

			self.insert_entries(row)
    def test_delivery_note_no_gl_entry(self):
        set_perpetual_inventory(0)
        self.assertEqual(cint(frappe.defaults.get_global_default("auto_accounting_for_stock")), 0)

        make_stock_entry(target="_Test Warehouse - _TC", qty=5, basic_rate=100)

        stock_queue = json.loads(
            get_previous_sle(
                {
                    "item_code": "_Test Item",
                    "warehouse": "_Test Warehouse - _TC",
                    "posting_date": nowdate(),
                    "posting_time": nowtime(),
                }
            ).stock_queue
            or "[]"
        )

        dn = create_delivery_note()

        sle = frappe.get_doc("Stock Ledger Entry", {"voucher_type": "Delivery Note", "voucher_no": dn.name})

        self.assertEqual(sle.stock_value_difference, -1 * stock_queue[0][1])

        self.assertFalse(get_gl_entries("Delivery Note", dn.name))
Exemple #5
0
    def set_actual_qty(self):
        allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock"))

        for d in self.get("items"):
            previous_sle = get_previous_sle(
                {
                    "item_code": d.item_code,
                    "warehouse": d.s_warehouse or d.t_warehouse,
                    "posting_date": self.posting_date,
                    "posting_time": self.posting_time,
                }
            )

            # get actual stock at source warehouse
            d.actual_qty = previous_sle.get("qty_after_transaction") or 0

            # validate qty during submit
            if d.docstatus == 1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
                frappe.throw(
                    _(
                        """Row {0}: Qty not avalable in warehouse {1} on {2} {3}.
					Available Qty: {4}, Transfer Qty: {5}"""
                    ).format(d.idx, d.s_warehouse, self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty),
                    NegativeStockError,
                )
Exemple #6
0
    def set_actual_qty(self):
        allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock"))

        for d in self.get("items"):
            previous_sle = get_previous_sle(
                {
                    "item_code": d.item_code,
                    "warehouse": d.s_warehouse or d.t_warehouse,
                    "posting_date": self.posting_date,
                    "posting_time": self.posting_time,
                }
            )

            # get actual stock at source warehouse
            d.actual_qty = previous_sle.get("qty_after_transaction") or 0

            # validate qty during submit
            if d.docstatus == 1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
                frappe.throw(
                    _(
                        "Row {0}: Qty not available for {4} in warehouse {1} at posting time of the entry ({2} {3})".format(
                            d.idx,
                            frappe.bold(d.s_warehouse),
                            formatdate(self.posting_date),
                            format_time(self.posting_time),
                            frappe.bold(d.item_code),
                        )
                    )
                    + "<br><br>"
                    + _("Available qty is {0}, you need {1}").format(
                        frappe.bold(d.actual_qty), frappe.bold(d.transfer_qty)
                    ),
                    NegativeStockError,
                    title=_("Insufficient Stock"),
                )
	def update_stock_ledger(self):
		"""	find difference between current and expected entries
			and create stock ledger entries based on the difference"""
		from erpnext.stock.stock_ledger import get_previous_sle

		for row in self.items:
			previous_sle = get_previous_sle({
				"item_code": row.item_code,
				"warehouse": row.warehouse,
				"posting_date": self.posting_date,
				"posting_time": self.posting_time
			})
			if previous_sle:
				if row.qty in ("", None):
					row.qty = previous_sle.get("qty_after_transaction", 0)

				if row.valuation_rate in ("", None):
					row.valuation_rate = previous_sle.get("valuation_rate", 0)

			if row.qty and not row.valuation_rate:
				frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code))

			if ((previous_sle and row.qty == previous_sle.get("qty_after_transaction")
				and row.valuation_rate == previous_sle.get("valuation_rate"))
				or (not previous_sle and not row.qty)):
					continue

			self.insert_entries(row)
	def _test_reco_sle_gle(self, valuation_method):
		set_perpetual_inventory()
		# [[qty, valuation_rate, posting_date,
		#		posting_time, expected_stock_value, bin_qty, bin_valuation]]
		input_data = [
			[50, 1000, "2012-12-26", "12:00"],
			[25, 900, "2012-12-26", "12:00"],
			["", 1000, "2012-12-20", "12:05"],
			[20, "", "2012-12-26", "12:05"],
			[0, "", "2012-12-31", "12:10"]
		]

		for d in input_data:
			repost_stock_as_per_valuation_method(valuation_method)

			last_sle = get_previous_sle({
				"item_code": "_Test Item",
				"warehouse": "_Test Warehouse - _TC",
				"posting_date": d[2],
				"posting_time": d[3]
			})

			# submit stock reconciliation
			stock_reco = create_stock_reconciliation(qty=d[0], rate=d[1],
				posting_date=d[2], posting_time=d[3])

			# check stock value
			sle = frappe.db.sql("""select * from `tabStock Ledger Entry`
				where voucher_type='Stock Reconciliation' and voucher_no=%s""", stock_reco.name, as_dict=1)

			qty_after_transaction = flt(d[0]) if d[0] != "" else flt(last_sle.get("qty_after_transaction"))

			valuation_rate = flt(d[1]) if d[1] != "" else flt(last_sle.get("valuation_rate"))

			if qty_after_transaction == last_sle.get("qty_after_transaction") \
				and valuation_rate == last_sle.get("valuation_rate"):
					self.assertFalse(sle)
			else:
				self.assertEqual(sle[0].qty_after_transaction, qty_after_transaction)
				self.assertEqual(sle[0].stock_value, qty_after_transaction * valuation_rate)

				# no gl entries
				self.assertTrue(frappe.db.get_value("Stock Ledger Entry",
					{"voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name}))
				self.assertFalse(get_stock_and_account_difference(["_Test Account Stock In Hand - _TC"]))

			stock_reco.cancel()

			self.assertFalse(frappe.db.get_value("Stock Ledger Entry",
				{"voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name}))

			self.assertFalse(frappe.db.get_value("GL Entry",
				{"voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name}))

			set_perpetual_inventory(0)
Exemple #9
0
	def get_stock_and_rate(self, force=False):
		"""get stock and incoming rate on posting date"""

		raw_material_cost = 0.0

		if not self.posting_date or not self.posting_time:
			frappe.throw(_("Posting date and posting time is mandatory"))

		allow_negative_stock = cint(frappe.db.get_default("allow_negative_stock"))

		for d in self.get('mtn_details'):
			d.transfer_qty = flt(d.transfer_qty)

			args = frappe._dict({
				"item_code": d.item_code,
				"warehouse": d.s_warehouse or d.t_warehouse,
				"posting_date": self.posting_date,
				"posting_time": self.posting_time,
				"qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty,
				"serial_no": d.serial_no
			})

			# get actual stock at source warehouse
			d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0

			# validate qty during submit
			if d.docstatus==1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
				frappe.throw(_("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}.
					Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse,
					self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty))

			# get incoming rate
			if not d.bom_no:
				if not flt(d.incoming_rate) or d.s_warehouse or self.purpose == "Sales Return" or force:
					incoming_rate = flt(self.get_incoming_rate(args), self.precision("incoming_rate", d))
					if incoming_rate > 0:
						d.incoming_rate = incoming_rate
				d.amount = flt(flt(d.transfer_qty) * flt(d.incoming_rate), self.precision("amount", d))
				if not d.t_warehouse:
					raw_material_cost += flt(d.amount)

		# set incoming rate for fg item
		if self.purpose in ["Manufacture", "Repack"]:
			number_of_fg_items = len([t.t_warehouse for t in self.get("mtn_details") if t.t_warehouse])
			for d in self.get("mtn_details"):
				if d.bom_no or (d.t_warehouse and number_of_fg_items == 1):
					if not flt(d.incoming_rate) or force:
						operation_cost_per_unit = 0
						if d.bom_no:
							bom = frappe.db.get_value("BOM", d.bom_no, ["operating_cost", "quantity"], as_dict=1)
							operation_cost_per_unit = flt(bom.operating_cost) / flt(bom.quantity)
						d.incoming_rate = flt(operation_cost_per_unit +
							(raw_material_cost + flt(self.total_fixed_cost)) / flt(d.transfer_qty), self.precision("incoming_rate", d))
					d.amount = flt(flt(d.transfer_qty) * flt(d.incoming_rate), self.precision("transfer_qty", d))
					break
def get_qty_after_transaction(**args):
	args = frappe._dict(args)

	last_sle = get_previous_sle({
		"item_code": args.item_code or "_Test Item",
		"warehouse": args.warehouse or "_Test Warehouse - _TC",
		"posting_date": args.posting_date or nowdate(),
		"posting_time": args.posting_time or nowtime()
	})

	return flt(last_sle.get("qty_after_transaction"))
Exemple #11
0
    def get_warehouse_details(self, args):
        ret = {}
        if args.get("warehouse") and args.get("item_code"):
            args.update({"posting_date": self.posting_date, "posting_time": self.posting_time})
            args = frappe._dict(args)

            ret = {
                "actual_qty": get_previous_sle(args).get("qty_after_transaction") or 0,
                "basic_rate": get_incoming_rate(args),
            }
        return ret
Exemple #12
0
def get_warehouse_details(args):
    if isinstance(args, basestring):
        args = json.loads(args)

    args = frappe._dict(args)

    ret = {}
    if args.warehouse and args.item_code:
        args.update({"posting_date": args.posting_date, "posting_time": args.posting_time})
        ret = {
            "actual_qty": get_previous_sle(args).get("qty_after_transaction") or 0,
            "basic_rate": get_incoming_rate(args),
        }

    return ret
Exemple #13
0
def get_opening_balance(filters, columns):
	if not (filters.item_code and filters.warehouse and filters.from_date):
		return

	from erpnext.stock.stock_ledger import get_previous_sle
	last_entry = get_previous_sle({
		"item_code": filters.item_code,
		"warehouse_condition": get_warehouse_condition(filters.warehouse),
		"posting_date": filters.from_date,
		"posting_time": "00:00:00"
	})
	row = [""]*len(columns)
	row[1] = _("'Opening'")
	for i, v in ((9, 'qty_after_transaction'), (11, 'valuation_rate'), (12, 'stock_value')):
			row[i] = last_entry.get(v, 0)

	return row
Exemple #14
0
	def get_stock_and_rate(self, force=False):
		"""get stock and incoming rate on posting date"""

		raw_material_cost = 0.0

		if not self.posting_date or not self.posting_time:
			frappe.throw(_("Posting date and posting time is mandatory"))

		allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock"))

		for d in self.get('items'):
			d.transfer_qty = flt(d.transfer_qty)

			args = frappe._dict({
				"item_code": d.item_code,
				"warehouse": d.s_warehouse or d.t_warehouse,
				"posting_date": self.posting_date,
				"posting_time": self.posting_time,
				"qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty,
				"serial_no": d.serial_no,
			})

			# get actual stock at source warehouse
			d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0

			# validate qty during submit
			if d.docstatus==1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
				frappe.throw(_("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}.
					Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse,
					self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty), NegativeStockError)

			# get incoming rate
			if not d.bom_no:
				if not flt(d.incoming_rate) or d.s_warehouse or self.purpose == "Sales Return" or force:
					incoming_rate = flt(self.get_incoming_rate(args), self.precision("incoming_rate", d))
					if incoming_rate > 0:
						d.incoming_rate = incoming_rate

				d.amount = flt(flt(d.transfer_qty) * flt(d.incoming_rate), d.precision("amount"))
				if not d.t_warehouse:
					raw_material_cost += flt(d.amount)


		self.add_operation_cost(raw_material_cost, force)
Exemple #15
0
	def test_delivery_note_no_gl_entry(self):
		company = frappe.db.get_value('Warehouse', '_Test Warehouse - _TC', 'company')
		set_perpetual_inventory(0, company)
		make_stock_entry(target="_Test Warehouse - _TC", qty=5, basic_rate=100)

		stock_queue = json.loads(get_previous_sle({
			"item_code": "_Test Item",
			"warehouse": "_Test Warehouse - _TC",
			"posting_date": nowdate(),
			"posting_time": nowtime()
		}).stock_queue or "[]")

		dn = create_delivery_note()

		sle = frappe.get_doc("Stock Ledger Entry", {"voucher_type": "Delivery Note", "voucher_no": dn.name})

		self.assertEqual(sle.stock_value_difference, -1*stock_queue[0][1])

		self.assertFalse(get_gl_entries("Delivery Note", dn.name))
Exemple #16
0
def get_opening_balance(filters, columns):
    if not (filters.item_code and filters.warehouse and filters.from_date):
        return

    from erpnext.stock.stock_ledger import get_previous_sle
    last_entry = get_previous_sle({
        "item_code": filters.item_code,
        "warehouse": filters.warehouse,
        "posting_date": filters.from_date,
        "posting_time": "00:00:00"
    })

    row = [""] * len(columns)
    row[1] = _("'Opening'")
    for i, v in ((9, 'qty_after_transaction'), (11, 'valuation_rate'),
                 (12, 'stock_value')):
        row[i] = last_entry.get(v, 0)

    return row
Exemple #17
0
	def set_actual_qty(self):
		allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock"))

		for d in self.get('items'):
			previous_sle = get_previous_sle({
				"item_code": d.item_code,
				"warehouse": d.s_warehouse or d.t_warehouse,
				"posting_date": self.posting_date,
				"posting_time": self.posting_time
			})

			# get actual stock at source warehouse
			d.actual_qty = previous_sle.get("qty_after_transaction") or 0

			# validate qty during submit
			if d.docstatus==1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
				frappe.throw(_("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}.
					Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse,
					self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty), NegativeStockError)
def get_rate(item_code, warehouse, based_on, price_list):
    time = frappe.utils.now_datetime().time()
    date = frappe.utils.now_datetime().date()
    balance = 0
    if warehouse:
        previous_sle = get_previous_sle({
            "item_code": item_code,
            "warehouse": warehouse,
            "posting_date": date,
            "posting_time": time
        })
        # get actual stock at source warehouse
        balance = previous_sle.get("qty_after_transaction") or 0

    condition = ""
    if price_list == "Standard Buying":
        condition += " and buying = 1 "
    elif price_list == "Standard Selling":
        condition += " and selling = 1 "

    query = """ SELECT * FROM `tabItem Price` WHERE item_code=%s {0} ORDER BY valid_from DESC LIMIT 1""".format(
        condition)

    item_price = frappe.db.sql(query, item_code, as_dict=1)
    rate = item_price[0].price_list_rate if len(item_price) > 0 else 0
    print(based_on)
    if based_on == "Valuation Rate":
        print("WALA DIR")
        item_record = frappe.db.sql(
            """ SELECT * FROM `tabItem` WHERE item_code=%s""",
            item_code,
            as_dict=1)
        rate = item_record[0].valuation_rate if len(item_record) > 0 else 0

    if based_on == "Last Purchase Rate":
        print("WALA DIR")
        item_record = frappe.db.sql(
            """ SELECT * FROM `tabItem` WHERE item_code=%s""",
            item_code,
            as_dict=1)
        rate = item_record[0].last_purchase_rate if len(item_record) > 0 else 0

    return rate, balance
Exemple #19
0
def get_warehouse_details(args):
    if isinstance(args, basestring):
        args = json.loads(args)

    args = frappe._dict(args)

    ret = {}
    if args.warehouse and args.item_code:
        args.update({
            "posting_date": args.posting_date,
            "posting_time": args.posting_time,
        })
        ret = {
            "actual_qty": get_previous_sle(args).get("qty_after_transaction")
            or 0,
            "basic_rate": get_incoming_rate(args)
        }

    return ret
Exemple #20
0
def get_incoming_rate(args):
	"""Get Incoming Rate based on valuation method"""
	from erpnext.stock.stock_ledger import get_previous_sle

	in_rate = 0
	if (args.get("serial_no") or "").strip():
		in_rate = get_avg_purchase_rate(args.get("serial_no"))
	else:
		valuation_method = get_valuation_method(args.get("item_code"))
		previous_sle = get_previous_sle(args)
		if valuation_method == 'FIFO':
			if not previous_sle:
				return 0.0
			previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]')
			in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0
		elif valuation_method == 'Moving Average':
			in_rate = previous_sle.get('valuation_rate') or 0

	return in_rate
Exemple #21
0
def get_incoming_rate(args):
	"""Get Incoming Rate based on valuation method"""
	from erpnext.stock.stock_ledger import get_previous_sle

	in_rate = 0
	if (args.get("serial_no") or "").strip():
		in_rate = get_avg_purchase_rate(args.get("serial_no"))
	else:
		valuation_method = get_valuation_method(args.get("item_code"))
		previous_sle = get_previous_sle(args)
		if valuation_method == 'FIFO':
			if not previous_sle:
				return 0.0
			previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]')
			in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0
		elif valuation_method == 'Moving Average':
			in_rate = previous_sle.get('valuation_rate') or 0

	return in_rate
	def get_stock_and_rate(self):
		"""get stock and incoming rate on posting date"""
		for d in self.get('mtn_details'):
			args = frappe._dict({
				"item_code": d.item_code,
				"warehouse": d.s_warehouse or d.t_warehouse,
				"posting_date": self.posting_date,
				"posting_time": self.posting_time,
				"qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty,
				"serial_no": d.serial_no,
				"bom_no": d.bom_no,
			})
			# get actual stock at source warehouse
			d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0

			# get incoming rate
			if not flt(d.incoming_rate):
				d.incoming_rate = self.get_incoming_rate(args)

			d.amount = flt(d.transfer_qty) * flt(d.incoming_rate)
Exemple #23
0
def get_stock_balance(item_code, warehouse, posting_date=None, posting_time=None, with_valuation_rate=False):
	"""Returns stock balance quantity at given warehouse on given posting date or current date.

	If `with_valuation_rate` is True, will return tuple (qty, rate)"""

	from erpnext.stock.stock_ledger import get_previous_sle

	if not posting_date: posting_date = nowdate()
	if not posting_time: posting_time = nowtime()

	last_entry = get_previous_sle({
		"item_code": item_code,
		"warehouse":warehouse,
		"posting_date": posting_date,
		"posting_time": posting_time })

	if with_valuation_rate:
		return (last_entry.qty_after_transaction, last_entry.valuation_rate) if last_entry else (0.0, 0.0)
	else:
		return last_entry.qty_after_transaction if last_entry else 0.0
Exemple #24
0
	def get_stock_and_rate(self):
		"""get stock and incoming rate on posting date"""
		for d in getlist(self.doclist, 'mtn_details'):
			args = frappe._dict({
				"item_code": d.item_code,
				"warehouse": d.s_warehouse or d.t_warehouse,
				"posting_date": self.doc.posting_date,
				"posting_time": self.doc.posting_time,
				"qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty,
				"serial_no": d.serial_no,
				"bom_no": d.bom_no,
			})
			# get actual stock at source warehouse
			d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0
			
			# get incoming rate
			if not flt(d.incoming_rate):
				d.incoming_rate = self.get_incoming_rate(args)
				
			d.amount = flt(d.transfer_qty) * flt(d.incoming_rate)
Exemple #25
0
def get_opening_balance(filters, columns):
	if not (filters.item_code and filters.warehouse and filters.from_date):
		return

	from erpnext.stock.stock_ledger import get_previous_sle
	last_entry = get_previous_sle({
		"item_code": filters.item_code,
		"warehouse_condition": get_warehouse_condition(filters.warehouse),
		"posting_date": filters.from_date,
		"posting_time": "00:00:00"
	})

	row = {
		"item_code": _("'Opening'"),
		"qty_after_transaction": last_entry.get("qty_after_transaction", 0),
		"valuation_rate": last_entry.get("valuation_rate", 0),
		"stock_value": last_entry.get("stock_value", 0)
	}

	return row
Exemple #26
0
def get_stock_balance(item_code, warehouse, posting_date=None, posting_time=None, with_valuation_rate=False):
	"""Returns stock balance quantity at given warehouse on given posting date or current date.

	If `with_valuation_rate` is True, will return tuple (qty, rate)"""

	from erpnext.stock.stock_ledger import get_previous_sle

	if not posting_date: posting_date = nowdate()
	if not posting_time: posting_time = nowtime()

	last_entry = get_previous_sle({
		"item_code": item_code,
		"warehouse":warehouse,
		"posting_date": posting_date,
		"posting_time": posting_time })

	if with_valuation_rate:
		return (last_entry.qty_after_transaction, last_entry.valuation_rate) if last_entry else (0.0, 0.0)
	else:
		return last_entry.qty_after_transaction or 0.0
Exemple #27
0
	def radpp_make_entry(self, args, allow_negative_stock=False, via_landed_cost_voucher=False):
		args.update({"doctype": "Stock Ledger Entry"})
	
		sle = frappe.get_doc(args)
		sle.flags.ignore_permissions = 1
		sle.allow_negative_stock=allow_negative_stock
		sle.via_landed_cost_voucher = via_landed_cost_voucher
		sle.valuation_rate = flt(sle.valuation_rate)
		sle.insert()
		
		parent = frappe.get_doc(sle.voucher_type,sle.voucher_no)
		
		# assert
		previous_sle_args = {
			"item_code": sle.item_code,
			"warehouse": sle.warehouse,
			"posting_date": parent.posting_date,
			"posting_time": parent.posting_time,
			"sle": sle.name
		}
		from erpnext.stock.stock_ledger import get_previous_sle
		previous_sle = get_previous_sle(previous_sle_args)
		prev_stock_value = (previous_sle.stock_value or 0.0) if previous_sle else 0.0
		sle.valuation_rate = args['valuation_rate']
		sle.qty_after_transaction = args['qty_after_transaction']
		sle.stock_queue = [[sle.qty_after_transaction, sle.valuation_rate]]
		sle.stock_value = flt(sle.qty_after_transaction) * flt(sle.valuation_rate)
			
		# rounding as per precision
		sle.stock_value = flt(sle.stock_value, sle.precision)

		stock_value_difference = sle.stock_value - prev_stock_value
		sle.prev_stock_value = sle.stock_value

		# update current sle
		sle.stock_queue = json.dumps(sle.stock_queue)
		sle.stock_value_difference = stock_value_difference
		frappe.get_doc(sle).db_update()	
		sle.save()
		sle.submit()
		return sle.name
Exemple #28
0
def get_opening_balance(filters, _columns, date, balance_type, item_code):
    if not (item_code and filters.warehouse and date):
        return

    from erpnext.stock.stock_ledger import get_previous_sle
    last_entry = get_previous_sle({
        "item_code":
        item_code,
        "warehouse_condition":
        get_warehouse_condition(filters.warehouse),
        "posting_date":
        date,
        "posting_time":
        "00:00:00"
    })
    row = {}
    row["voucher_type"] = _(balance_type)
    for dummy, v in ((9, 'actual_qty')):
        row[v] = last_entry.get(v, 0)

    return row
Exemple #29
0
def get_stock_balance(item_code,
                      warehouse,
                      posting_date=None,
                      posting_time=None,
                      with_valuation_rate=False,
                      with_serial_no=False):
    """Returns stock balance quantity at given warehouse on given posting date or current date.

	If `with_valuation_rate` is True, will return tuple (qty, rate)"""

    from erpnext.stock.stock_ledger import get_previous_sle

    if not posting_date: posting_date = nowdate()
    if not posting_time: posting_time = nowtime()

    args = {
        "item_code": item_code,
        "warehouse": warehouse,
        "posting_date": posting_date,
        "posting_time": posting_time
    }

    last_entry = get_previous_sle(args)

    if with_valuation_rate:
        if with_serial_no:
            serial_nos = last_entry.get("serial_no")

            if (serial_nos and len(get_serial_nos_data(serial_nos)) <
                    last_entry.qty_after_transaction):
                serial_nos = get_serial_nos_data_after_transactions(args)

            return ((last_entry.qty_after_transaction,
                     last_entry.valuation_rate, serial_nos) if last_entry else
                    (0.0, 0.0, 0.0))
        else:
            return (last_entry.qty_after_transaction,
                    last_entry.valuation_rate) if last_entry else (0.0, 0.0)
    else:
        return last_entry.qty_after_transaction if last_entry else 0.0
	def insert_stock_ledger_entries(self):
		"""	find difference between current and expected entries
			and create stock ledger entries based on the difference"""
		from erpnext.stock.utils import get_valuation_method
		from erpnext.stock.stock_ledger import get_previous_sle
			
		row_template = ["item_code", "warehouse", "qty", "valuation_rate"]
		
		if not self.doc.reconciliation_json:
			msgprint(_("""Stock Reconciliation file not uploaded"""), raise_exception=1)
		
		data = json.loads(self.doc.reconciliation_json)
		for row_num, row in enumerate(data[data.index(self.head_row)+1:]):
			row = frappe._dict(zip(row_template, row))
			row["row_num"] = row_num
			previous_sle = get_previous_sle({
				"item_code": row.item_code,
				"warehouse": row.warehouse,
				"posting_date": self.doc.posting_date,
				"posting_time": self.doc.posting_time
			})

			# check valuation rate mandatory
			if row.qty != "" and not row.valuation_rate and \
					flt(previous_sle.get("qty_after_transaction")) <= 0:
				frappe.throw(_("As existing qty for item: ") + row.item_code + 
					_(" at warehouse: ") + row.warehouse +
					_(" is less than equals to zero in the system, valuation rate is mandatory for this item"))
			
			change_in_qty = row.qty != "" and \
				(flt(row.qty) - flt(previous_sle.get("qty_after_transaction")))
			
			change_in_rate = row.valuation_rate != "" and \
				(flt(row.valuation_rate) - flt(previous_sle.get("valuation_rate")))
			
			if get_valuation_method(row.item_code) == "Moving Average":
				self.sle_for_moving_avg(row, previous_sle, change_in_qty, change_in_rate)
					
			else:
				self.sle_for_fifo(row, previous_sle, change_in_qty, change_in_rate)
def update_stock_ledger(self):
    """	find difference between current and expected entries
            and create stock ledger entries based on the difference"""
    from erpnext.stock.stock_ledger import get_previous_sle

    sl_entries = []
    for row in self.items:
        item = frappe.get_doc("Item", row.item_code)
        if item.has_serial_no or item.has_batch_no:
            self.get_sle_for_serialized_items(row, sl_entries)
        else:
            previous_sle = get_previous_sle({
                "item_code": row.item_code,
                "warehouse": row.warehouse,
                "posting_date": self.posting_date,
                "posting_time": self.posting_time
            })

            if previous_sle:
                if row.qty in ("", None):
                    row.qty = previous_sle.get("qty_after_transaction", 0)

                if row.valuation_rate in ("", None):
                    row.valuation_rate = previous_sle.get("valuation_rate", 0)

            if row.qty and not row.valuation_rate:
                frappe.throw(
                    _("Valuation Rate required for Item {0} at row {1}").
                    format(row.item_code, row.idx))

            if ((previous_sle
                 and row.qty == previous_sle.get("qty_after_transaction") and
                 (row.valuation_rate == previous_sle.get("valuation_rate")
                  or row.qty == 0)) or (not previous_sle and not row.qty)):
                continue

            sl_entries.append(self.get_sle_for_items(row))

    if sl_entries:
        self.make_sl_entries(sl_entries)
Exemple #32
0
def execute(filters=None):
    columns, data = get_columns(filters), []
    time = frappe.utils.now_datetime().time()
    date = frappe.utils.now_datetime().date()
    average_stock_dates = get_dates(filters.get("average_stock"))
    item_group = filters.get("item_group")
    item = filters.get("item")
    condition = ""
    if item_group:
        condition += " and item_group='{0}'".format(item_group)
    if item:
        condition += " and name='{0}'".format(item)
    query = """ SELECT * FROM `tabItem` WHERE disabled=0 {0}""".format(
        condition)
    items = frappe.db.sql(query, as_dict=1)
    for item in items:
        query = """ SELECT *
					FROM `tabStock Ledger Entry` 
					WHERE item_code='{0}' and actual_qty < 0""".format(item.name)
        sle_check = frappe.db.sql(query, as_dict=1)
        if len(sle_check) > 0:
            obj = {
                "item_code": item.name,
            }
            get_sle(item.name, average_stock_dates, obj)
            obj['current_stock'] = get_previous_sle({
                "item_code": item.name,
                "posting_date": date,
                "posting_time": time
            }).get("qty_after_transaction") or 0

            obj['stock_position'] = obj['current_stock'] - obj[
                'average_sell_stock']
            obj['stock_month'] = obj['current_stock'] / (
                obj['average_sale_per_month']
                if obj['average_sale_per_month'] > 0 else 1)
            data.append(obj)

    print(data)
    return columns, data
Exemple #33
0
def get_incoming_rate(args):
    """Get Incoming Rate based on valuation method"""
    if isinstance(args, basestring):
        args = json.loads(args)

    in_rate = 0
    batch_wise_rate = cint(
        frappe.db.get_single_value(
            "Stock Settings", 'exact_cost_valuation_for_batch_wise_items'))

    if args.get("batch_no") and batch_wise_rate:
        in_rate = get_batch_rate(args.get("batch_no"))
    elif (args.get("serial_no") or "").strip():
        in_rate = get_avg_purchase_rate(args.get("serial_no"))
    else:
        valuation_method = get_valuation_method(args.get("item_code"))
        previous_sle = get_previous_sle(args)
        if valuation_method == 'FIFO':
            if previous_sle:
                previous_stock_queue = json.loads(
                    previous_sle.get('stock_queue', '[]') or '[]')
                in_rate = get_fifo_rate(previous_stock_queue,
                                        args.get("qty")
                                        or 0) if previous_stock_queue else 0
        elif valuation_method == 'Moving Average':
            in_rate = previous_sle.get('valuation_rate') or 0

    if not in_rate:
        voucher_no = args.get('voucher_no') or args.get('name')
        in_rate = get_valuation_rate(args.get('item_code'),
                                     args.get('warehouse'),
                                     args.get('voucher_type'),
                                     voucher_no,
                                     args.get('allow_zero_valuation'),
                                     currency=erpnext.get_company_currency(
                                         args.get('company')),
                                     company=args.get('company'))

    return in_rate
Exemple #34
0
	def set_actual_qty(self):
		allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock"))

		for d in self.get('items'):
			previous_sle = get_previous_sle({
				"item_code": d.item_code,
				"warehouse": d.s_warehouse or d.t_warehouse,
				"posting_date": self.posting_date,
				"posting_time": self.posting_time
			})

			# get actual stock at source warehouse
			d.actual_qty = previous_sle.get("qty_after_transaction") or 0

			# validate qty during submit
			if d.docstatus==1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
				frappe.throw(_("Row {0}: Qty not available for {4} in warehouse {1} at posting time of the entry ({2} {3})").format(d.idx,
					frappe.bold(d.s_warehouse), formatdate(self.posting_date),
					format_time(self.posting_time), frappe.bold(d.item_code))
					+ '<br><br>' + _("Available qty is {0}, you need {1}").format(frappe.bold(d.actual_qty),
						frappe.bold(d.transfer_qty)),
					NegativeStockError, title=_('Insufficient Stock'))
Exemple #35
0
    def set_actual_qty(self):
        allow_negative_stock = cint(
            frappe.db.get_value("Stock Settings", None,
                                "allow_negative_stock"))

        allow_start = True
        for d in self.get('items'):
            previous_sle = get_previous_sle({
                "item_code": d.item_code,
                "warehouse": self.warehouse,
                "posting_date": nowdate(),
                "posting_time": nowtime()
            })

            # get actual stock at source warehouse
            d.actual_qty = previous_sle.get("qty_after_transaction") or 0

            # validate qty
            if not allow_negative_stock and d.actual_qty < d.qty:
                allow_start = False

        return allow_start
	def insert_stock_ledger_entries(self):
		"""	find difference between current and expected entries
			and create stock ledger entries based on the difference"""
		from erpnext.stock.utils import get_valuation_method
		from erpnext.stock.stock_ledger import get_previous_sle

		row_template = ["item_code", "warehouse", "qty", "valuation_rate"]

		if not self.reconciliation_json:
			msgprint(_("""Stock Reconciliation file not uploaded"""), raise_exception=1)

		data = json.loads(self.reconciliation_json)
		for row_num, row in enumerate(data[data.index(self.head_row)+1:]):
			row = frappe._dict(zip(row_template, row))
			row["row_num"] = row_num
			previous_sle = get_previous_sle({
				"item_code": row.item_code,
				"warehouse": row.warehouse,
				"posting_date": self.posting_date,
				"posting_time": self.posting_time
			})

			# check valuation rate mandatory
			if row.qty != "" and not row.valuation_rate and \
					flt(previous_sle.get("qty_after_transaction")) <= 0:
				frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code))

			change_in_qty = row.qty != "" and \
				(flt(row.qty) - flt(previous_sle.get("qty_after_transaction")))

			change_in_rate = row.valuation_rate != "" and \
				(flt(row.valuation_rate) - flt(previous_sle.get("valuation_rate")))

			if get_valuation_method(row.item_code) == "Moving Average":
				self.sle_for_moving_avg(row, previous_sle, change_in_qty, change_in_rate)

			else:
				self.sle_for_fifo(row, previous_sle, change_in_qty, change_in_rate)
    def update_stock_ledger(self):
        """	find difference between current and expected entries
			and create stock ledger entries based on the difference"""
        from erpnext.stock.stock_ledger import get_previous_sle

        row_template = ["item_code", "warehouse", "qty", "valuation_rate"]

        if not self.reconciliation_json:
            msgprint(_("""Stock Reconciliation file not uploaded"""),
                     raise_exception=1)

        data = json.loads(self.reconciliation_json)
        for row_num, row in enumerate(data[data.index(self.head_row) + 1:]):
            row = frappe._dict(zip(row_template, row))
            row["row_num"] = row_num

            if row.qty in ("", None) or row.valuation_rate in ("", None):
                previous_sle = get_previous_sle({
                    "item_code":
                    row.item_code,
                    "warehouse":
                    row.warehouse,
                    "posting_date":
                    self.posting_date,
                    "posting_time":
                    self.posting_time
                })

                if row.qty in ("", None):
                    row.qty = previous_sle.get("qty_after_transaction")

                if row.valuation_rate in ("", None):
                    row.valuation_rate = previous_sle.get("valuation_rate")

            # if row.qty and not row.valuation_rate:
            # 	frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code))

            self.insert_entries(row)
Exemple #38
0
def get_incoming_rate(args):
	"""Get Incoming Rate based on valuation method"""
	from erpnext.stock.stock_ledger import get_previous_sle

	in_rate = 0
	if args.get("serial_no"):
		in_rate = get_avg_purchase_rate(args.get("serial_no"))
	elif args.get("bom_no"):
		result = frappe.db.sql("""select ifnull(total_cost, 0) / ifnull(quantity, 1)
			from `tabBOM` where name = %s and docstatus=1 and is_active=1""", args.get("bom_no"))
		in_rate = result and flt(result[0][0]) or 0
	else:
		valuation_method = get_valuation_method(args.get("item_code"))
		previous_sle = get_previous_sle(args)
		if valuation_method == 'FIFO':
			if not previous_sle:
				return 0.0
			previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]')
			in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0
		elif valuation_method == 'Moving Average':
			in_rate = previous_sle.get('valuation_rate') or 0

	return in_rate
Exemple #39
0
def get_incoming_rate(args, raise_error_if_no_rate=True):
    """Get Incoming Rate based on valuation method"""
    from erpnext.stock.stock_ledger import get_previous_sle, get_valuation_rate

    if isinstance(args, string_types):
        args = json.loads(args)

    in_rate = 0
    if (args.get("serial_no") or "").strip():
        in_rate = get_avg_purchase_rate(args.get("serial_no"))
    else:
        valuation_method = get_valuation_method(args.get("item_code"))
        previous_sle = get_previous_sle(args)
        if valuation_method == "FIFO":
            if previous_sle:
                previous_stock_queue = json.loads(
                    previous_sle.get("stock_queue", "[]") or "[]")
                in_rate = (get_fifo_rate(previous_stock_queue,
                                         args.get("qty") or 0)
                           if previous_stock_queue else 0)
        elif valuation_method == "Moving Average":
            in_rate = previous_sle.get("valuation_rate") or 0

    if not in_rate:
        voucher_no = args.get("voucher_no") or args.get("name")
        in_rate = get_valuation_rate(
            args.get("item_code"),
            args.get("warehouse"),
            args.get("voucher_type"),
            voucher_no,
            args.get("allow_zero_valuation"),
            currency=erpnext.get_company_currency(args.get("company")),
            company=args.get("company"),
            raise_error_if_no_rate=raise_error_if_no_rate,
        )

    return flt(in_rate)
Exemple #40
0
	def add_exploded_items(self):
		exploded_items = {}
		for d in self.get('exploded_items'):
			exploded_items[d.item_code] = d
			
		self.set('exploded_items', [])

		from erpnext.stock.doctype.stock_entry.stock_entry import get_best_warehouse
		from erpnext.stock.stock_ledger import get_previous_sle
		posting_date = nowdate()
		posting_time = nowtime()
		
		
		for d in sorted(self.cur_exploded_items, key=itemgetter(0)):
			
			ch = self.append('exploded_items', {})
			for i in self.cur_exploded_items[d].keys():
				ch.set(i, self.cur_exploded_items[d][i])
			
			best_warehouse,enough_stock = get_best_warehouse(ch.item_code,ch.stock_qty,company = self.company)
			if best_warehouse:
				ch.actual_qty = get_previous_sle({
									"item_code": ch.item_code,
									"warehouse": best_warehouse,
									"posting_date": posting_date,
									"posting_time": posting_time
								}).get("qty_after_transaction") or 0
				
				
			else:
				ch.actual_qty = 0
			
			ch.source_warehouse = best_warehouse
			ch.amount = flt(ch.stock_qty) * flt(ch.rate)
			ch.qty_consumed_per_unit = flt(ch.stock_qty)
			if exploded_items.get(ch.item_code):
				ch.dutible = exploded_items[ch.item_code].dutible
	def get_incoming_rate(self, args):
		incoming_rate = 0
		if self.purpose == "Sales Return" and \
				(self.delivery_note_no or self.sales_invoice_no):
			sle = frappe.db.sql("""select name, posting_date, posting_time,
				actual_qty, stock_value, warehouse from `tabStock Ledger Entry`
				where voucher_type = %s and voucher_no = %s and
				item_code = %s limit 1""",
				((self.delivery_note_no and "Delivery Note" or "Sales Invoice"),
				self.delivery_note_no or self.sales_invoice_no, args.item_code), as_dict=1)
			if sle:
				args.update({
					"posting_date": sle[0].posting_date,
					"posting_time": sle[0].posting_time,
					"sle": sle[0].name,
					"warehouse": sle[0].warehouse,
				})
				previous_sle = get_previous_sle(args)
				incoming_rate = (flt(sle[0].stock_value) - flt(previous_sle.get("stock_value"))) / \
					flt(sle[0].actual_qty)
		else:
			incoming_rate = get_incoming_rate(args)

		return incoming_rate
Exemple #42
0
	def get_incoming_rate(self, args):
		incoming_rate = 0
		if self.doc.purpose == "Sales Return" and \
				(self.doc.delivery_note_no or self.doc.sales_invoice_no):
			sle = frappe.db.sql("""select name, posting_date, posting_time, 
				actual_qty, stock_value, warehouse from `tabStock Ledger Entry` 
				where voucher_type = %s and voucher_no = %s and 
				item_code = %s limit 1""", 
				((self.doc.delivery_note_no and "Delivery Note" or "Sales Invoice"),
				self.doc.delivery_note_no or self.doc.sales_invoice_no, args.item_code), as_dict=1)
			if sle:
				args.update({
					"posting_date": sle[0].posting_date,
					"posting_time": sle[0].posting_time,
					"sle": sle[0].name,
					"warehouse": sle[0].warehouse,
				})
				previous_sle = get_previous_sle(args)
				incoming_rate = (flt(sle[0].stock_value) - flt(previous_sle.get("stock_value"))) / \
					flt(sle[0].actual_qty)
		else:
			incoming_rate = get_incoming_rate(args)
			
		return incoming_rate
Exemple #43
0
    def get_stock_and_rate(self, force=False):
        """get stock and incoming rate on posting date"""

        raw_material_cost = 0.0

        if not self.posting_date or not self.posting_time:
            frappe.throw(_("Posting date and posting time is mandatory"))

        allow_negative_stock = cint(
            frappe.db.get_value("Stock Settings", None,
                                "allow_negative_stock"))

        for d in self.get('items'):
            d.transfer_qty = flt(d.transfer_qty)

            args = frappe._dict({
                "item_code":
                d.item_code,
                "warehouse":
                d.s_warehouse or d.t_warehouse,
                "posting_date":
                self.posting_date,
                "posting_time":
                self.posting_time,
                "qty":
                d.s_warehouse and -1 * d.transfer_qty or d.transfer_qty,
                "serial_no":
                d.serial_no,
            })

            # get actual stock at source warehouse
            d.actual_qty = get_previous_sle(args).get(
                "qty_after_transaction") or 0

            # validate qty during submit
            if d.docstatus == 1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
                frappe.throw(
                    _("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}.
					Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse,
                                                      self.posting_date,
                                                      self.posting_time,
                                                      d.actual_qty,
                                                      d.transfer_qty),
                    NegativeStockError)

            # get incoming rate
            if not d.bom_no:
                if not flt(
                        d.incoming_rate
                ) or d.s_warehouse or self.purpose == "Sales Return" or force:
                    incoming_rate = flt(self.get_incoming_rate(args),
                                        self.precision("incoming_rate", d))
                    if incoming_rate > 0:
                        d.incoming_rate = incoming_rate

                d.amount = flt(
                    flt(d.transfer_qty) * flt(d.incoming_rate),
                    d.precision("amount"))
                if not d.t_warehouse:
                    raw_material_cost += flt(d.amount)

        self.add_operation_cost(raw_material_cost, force)
Exemple #44
0
    def _test_reco_sle_gle(self, valuation_method):
        insert_existing_sle(warehouse='Stores - TCP1')
        company = frappe.db.get_value('Warehouse', 'Stores - TCP1', 'company')
        # [[qty, valuation_rate, posting_date,
        #		posting_time, expected_stock_value, bin_qty, bin_valuation]]
        input_data = [[50, 1000], [25, 900], ["", 1000], [20, ""], [0, ""]]

        for d in input_data:
            set_valuation_method("_Test Item", valuation_method)

            last_sle = get_previous_sle({
                "item_code": "_Test Item",
                "warehouse": "Stores - TCP1",
                "posting_date": nowdate(),
                "posting_time": nowtime()
            })

            # submit stock reconciliation
            stock_reco = create_stock_reconciliation(
                qty=d[0],
                rate=d[1],
                posting_date=nowdate(),
                posting_time=nowtime(),
                warehouse="Stores - TCP1",
                company=company,
                expense_account="Stock Adjustment - TCP1")

            # check stock value
            sle = frappe.db.sql("""select * from `tabStock Ledger Entry`
				where voucher_type='Stock Reconciliation' and voucher_no=%s""",
                                stock_reco.name,
                                as_dict=1)

            qty_after_transaction = flt(d[0]) if d[0] != "" else flt(
                last_sle.get("qty_after_transaction"))

            valuation_rate = flt(d[1]) if d[1] != "" else flt(
                last_sle.get("valuation_rate"))

            if qty_after_transaction == last_sle.get("qty_after_transaction") \
             and valuation_rate == last_sle.get("valuation_rate"):
                self.assertFalse(sle)
            else:
                self.assertEqual(flt(sle[0].qty_after_transaction, 1),
                                 flt(qty_after_transaction, 1))
                self.assertEqual(
                    flt(sle[0].stock_value, 1),
                    flt(qty_after_transaction * valuation_rate, 1))

                # no gl entries
                self.assertTrue(
                    frappe.db.get_value(
                        "Stock Ledger Entry", {
                            "voucher_type": "Stock Reconciliation",
                            "voucher_no": stock_reco.name
                        }))

                acc_bal, stock_bal, wh_list = get_stock_and_account_balance(
                    "Stock In Hand - TCP1", stock_reco.posting_date,
                    stock_reco.company)
                self.assertEqual(flt(acc_bal, 1), flt(stock_bal, 1))

                stock_reco.cancel()
    def update_stock_ledger(self):
        """	find difference between current and expected entries
			and create stock ledger entries based on the difference"""
        from erpnext.stock.stock_ledger import get_previous_sle

        sl_entries = []
        has_serial_no = False
        has_batch_no = False
        for row in self.items:
            item = frappe.get_doc("Item", row.item_code)
            if item.has_batch_no:
                has_batch_no = True

            if item.has_serial_no or item.has_batch_no:
                has_serial_no = True
                self.get_sle_for_serialized_items(row, sl_entries)
            else:
                if row.serial_no or row.batch_no:
                    frappe.throw(_("Row #{0}: Item {1} is not a Serialized/Batched Item. It cannot have a Serial No/Batch No against it.") \
                     .format(row.idx, frappe.bold(row.item_code)))

                previous_sle = get_previous_sle({
                    "item_code":
                    row.item_code,
                    "warehouse":
                    row.warehouse,
                    "posting_date":
                    self.posting_date,
                    "posting_time":
                    self.posting_time
                })

                if previous_sle:
                    if row.qty in ("", None):
                        row.qty = previous_sle.get("qty_after_transaction", 0)

                    if row.valuation_rate in ("", None):
                        row.valuation_rate = previous_sle.get(
                            "valuation_rate", 0)

                if row.qty and not row.valuation_rate:
                    frappe.throw(
                        _("Valuation Rate required for Item {0} at row {1}").
                        format(row.item_code, row.idx))

                if ((previous_sle
                     and row.qty == previous_sle.get("qty_after_transaction")
                     and
                     (row.valuation_rate == previous_sle.get("valuation_rate")
                      or row.qty == 0)) or (not previous_sle and not row.qty)):
                    continue

                sl_entries.append(self.get_sle_for_items(row))

        if sl_entries:
            if has_serial_no:
                sl_entries = self.merge_similar_item_serial_nos(sl_entries)

            allow_negative_stock = False
            if has_batch_no:
                allow_negative_stock = True

            self.make_sl_entries(sl_entries,
                                 allow_negative_stock=allow_negative_stock)

        if has_serial_no and sl_entries:
            self.update_valuation_rate_for_serial_no()
def get_opening_balance(filters, columns):
    #frappe.msgprint(frappe._('get_opening_balance ' + str(filters)))
    if not (filters.item_code and filters.company):
        return

    warehouse_list = []
    from erpnext.stock.stock_ledger import get_previous_sle
    if not (filters.item_code and filters.warehouse and filters.company):

        for warehouse in frappe.get_list("Warehouse", fields=["name"]):

            last_sle_for_each_warehouse = get_previous_sle({
                "item_code":
                filters.item_code,
                "warehouse":
                warehouse['name'],
                "posting_date":
                datetime.datetime.now().date(),
                "posting_time":
                datetime.datetime.now().time()
            })

            if last_sle_for_each_warehouse:
                warehouse_list.append({'warehouse': warehouse['name']})

    else:
        last_sle_for_each_warehouse = get_previous_sle({
            "item_code":
            filters.item_code,
            "warehouse":
            filters.warehouse,
            "posting_date":
            datetime.datetime.now().date(),
            "posting_time":
            datetime.datetime.now().time()
        })

        if last_sle_for_each_warehouse:
            warehouse_list.append({'warehouse': filters.warehouse})

    # frappe.msgprint(frappe._('len(warehouse_list) : ' + str(warehouse_list)))

    last_sle_for_each_warehouse = [
        get_previous_sle({
            "item_code": filters.item_code,
            "warehouse": warehouse['warehouse'],
            "posting_date": datetime.datetime.now().date(),
            "posting_time": datetime.datetime.now().time()
        }) for warehouse in warehouse_list
    ]

    rows = []
    for index, balance in enumerate(last_sle_for_each_warehouse):

        #frappe.msgprint(frappe._('balance[qty_after_transaction] : ' + str(balance.get('qty_after_transaction'))))
        dict_sle_value = {
            2: _("Opening"),
            5: balance['qty_after_transaction'],
            6: 0.0,
            8: balance['warehouse']
        }
        if index > 0:
            dict_sle_value[7] = balance[
                'qty_after_transaction'] + last_sle_for_each_warehouse[
                    index - 1]['qty_after_transaction']
        if index == 0:
            dict_sle_value[7] = balance['qty_after_transaction']
        if index < (len(last_sle_for_each_warehouse) - 1):
            next_ = last_sle_for_each_warehouse[index + 1]
        balance['qty_after_transaction'] = dict_sle_value[7]
        rows.append([
            dict_sle_value.get(x) if x in dict_sle_value else ""
            for x in range(len(columns))
        ])

    return rows
Exemple #47
0
    def get_stock_and_rate(self, force=False):
        """get stock and incoming rate on posting date"""

        raw_material_cost = 0.0

        if not self.posting_date or not self.posting_time:
            frappe.throw(_("Posting date and posting time is mandatory"))

        allow_negative_stock = cint(
            frappe.db.get_default("allow_negative_stock"))

        for d in self.get('mtn_details'):
            d.transfer_qty = flt(d.transfer_qty)

            args = frappe._dict({
                "item_code":
                d.item_code,
                "warehouse":
                d.s_warehouse or d.t_warehouse,
                "posting_date":
                self.posting_date,
                "posting_time":
                self.posting_time,
                "qty":
                d.s_warehouse and -1 * d.transfer_qty or d.transfer_qty,
                "serial_no":
                d.serial_no
            })

            # get actual stock at source warehouse
            d.actual_qty = get_previous_sle(args).get(
                "qty_after_transaction") or 0

            # validate qty during submit
            if d.docstatus == 1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty:
                frappe.throw(
                    _("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}.
					Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse,
                                                      self.posting_date,
                                                      self.posting_time,
                                                      d.actual_qty,
                                                      d.transfer_qty))

            # get incoming rate
            if not d.bom_no:
                if not flt(
                        d.incoming_rate
                ) or d.s_warehouse or self.purpose == "Sales Return" or force:
                    incoming_rate = flt(self.get_incoming_rate(args),
                                        self.precision("incoming_rate", d))
                    if incoming_rate > 0:
                        d.incoming_rate = incoming_rate
                d.amount = flt(
                    flt(d.transfer_qty) * flt(d.incoming_rate),
                    self.precision("amount", d))
                if not d.t_warehouse:
                    raw_material_cost += flt(d.amount)

        # set incoming rate for fg item
        if self.purpose in ["Manufacture", "Repack"]:
            number_of_fg_items = len([
                t.t_warehouse for t in self.get("mtn_details") if t.t_warehouse
            ])
            for d in self.get("mtn_details"):
                if d.bom_no or (d.t_warehouse and number_of_fg_items == 1):
                    if not flt(d.incoming_rate) or force:
                        operation_cost_per_unit = 0
                        if d.bom_no:
                            bom = frappe.db.get_value(
                                "BOM",
                                d.bom_no, ["operating_cost", "quantity"],
                                as_dict=1)
                            operation_cost_per_unit = flt(
                                bom.operating_cost) / flt(bom.quantity)
                        d.incoming_rate = flt(
                            operation_cost_per_unit +
                            (raw_material_cost + flt(self.total_fixed_cost)) /
                            flt(d.transfer_qty),
                            self.precision("incoming_rate", d))
                    d.amount = flt(
                        flt(d.transfer_qty) * flt(d.incoming_rate),
                        self.precision("transfer_qty", d))
                    break
Exemple #48
0
    def get_sle_for_serialized_items(self, row, sl_entries):
        from erpnext.stock.stock_ledger import get_previous_sle

        serial_nos = get_serial_nos(row.serial_no)

        # To issue existing serial nos
        if row.current_qty and (row.current_serial_no or row.batch_no):
            args = self.get_sle_for_items(row)
            args.update({
                'actual_qty': -1 * row.current_qty,
                'serial_no': row.current_serial_no,
                'batch_no': row.batch_no,
                'valuation_rate': row.current_valuation_rate
            })

            if row.current_serial_no:
                args.update({
                    'qty_after_transaction': 0,
                })

            sl_entries.append(args)

        for serial_no in serial_nos:
            args = self.get_sle_for_items(row, [serial_no])

            previous_sle = get_previous_sle({
                "item_code": row.item_code,
                "posting_date": self.posting_date,
                "posting_time": self.posting_time,
                "serial_no": serial_no
            })

            if previous_sle and row.warehouse != previous_sle.get("warehouse"):
                # If serial no exists in different warehouse

                new_args = args.copy()
                new_args.update({
                    'actual_qty':
                    -1,
                    'qty_after_transaction':
                    cint(previous_sle.get('qty_after_transaction')) - 1,
                    'warehouse':
                    previous_sle.get("warehouse", '') or row.warehouse,
                    'valuation_rate':
                    previous_sle.get("valuation_rate")
                })

                sl_entries.append(new_args)

        if row.qty:
            args = self.get_sle_for_items(row)

            args.update({
                'actual_qty': row.qty,
                'incoming_rate': row.valuation_rate,
                'valuation_rate': row.valuation_rate
            })

            sl_entries.append(args)

        if serial_nos == get_serial_nos(row.current_serial_no):
            # update valuation rate
            self.update_valuation_rate_for_serial_nos(row, serial_nos)
Exemple #49
0
    def test_item_cost_reposting(self):
        company = "_Test Company"

        # _Test Item for Reposting at Stores warehouse on 10-04-2020: Qty = 50, Rate = 100
        create_stock_reconciliation(item_code="_Test Item for Reposting",
                                    warehouse="Stores - _TC",
                                    qty=50,
                                    rate=100,
                                    company=company,
                                    expense_account="Stock Adjustment - _TC"
                                    if frappe.get_all("Stock Ledger Entry")
                                    else "Temporary Opening - _TC",
                                    posting_date='2020-04-10',
                                    posting_time='14:00')

        # _Test Item for Reposting at FG warehouse on 20-04-2020: Qty = 10, Rate = 200
        create_stock_reconciliation(item_code="_Test Item for Reposting",
                                    warehouse="Finished Goods - _TC",
                                    qty=10,
                                    rate=200,
                                    company=company,
                                    expense_account="Stock Adjustment - _TC"
                                    if frappe.get_all("Stock Ledger Entry")
                                    else "Temporary Opening - _TC",
                                    posting_date='2020-04-20',
                                    posting_time='14:00')

        # _Test Item for Reposting transferred from Stores to FG warehouse on 30-04-2020
        se = make_stock_entry(item_code="_Test Item for Reposting",
                              source="Stores - _TC",
                              target="Finished Goods - _TC",
                              company=company,
                              qty=10,
                              expense_account="Stock Adjustment - _TC"
                              if frappe.get_all("Stock Ledger Entry") else
                              "Temporary Opening - _TC",
                              posting_date='2020-04-30',
                              posting_time='14:00')
        target_wh_sle = frappe.db.get_value('Stock Ledger Entry', {
            "item_code": "_Test Item for Reposting",
            "warehouse": "Finished Goods - _TC",
            "voucher_type": "Stock Entry",
            "voucher_no": se.name
        }, ["valuation_rate"],
                                            as_dict=1)

        self.assertEqual(target_wh_sle.get("valuation_rate"), 150)

        # Repack entry on 5-5-2020
        repack = create_repack_entry(company=company,
                                     posting_date='2020-05-05',
                                     posting_time='14:00')

        finished_item_sle = frappe.db.get_value('Stock Ledger Entry', {
            "item_code": "_Test Finished Item for Reposting",
            "warehouse": "Finished Goods - _TC",
            "voucher_type": "Stock Entry",
            "voucher_no": repack.name
        }, ["incoming_rate", "valuation_rate"],
                                                as_dict=1)
        self.assertEqual(finished_item_sle.get("incoming_rate"), 540)
        self.assertEqual(finished_item_sle.get("valuation_rate"), 540)

        # Reconciliation for _Test Item for Reposting at Stores on 12-04-2020: Qty = 50, Rate = 150
        sr = create_stock_reconciliation(
            item_code="_Test Item for Reposting",
            warehouse="Stores - _TC",
            qty=50,
            rate=150,
            company=company,
            expense_account="Stock Adjustment - _TC"
            if frappe.get_all("Stock Ledger Entry") else
            "Temporary Opening - _TC",
            posting_date='2020-04-12',
            posting_time='14:00')

        # Check valuation rate of finished goods warehouse after back-dated entry at Stores
        target_wh_sle = get_previous_sle({
            "item_code": "_Test Item for Reposting",
            "warehouse": "Finished Goods - _TC",
            "posting_date": '2020-04-30',
            "posting_time": '14:00'
        })
        self.assertEqual(target_wh_sle.get("incoming_rate"), 150)
        self.assertEqual(target_wh_sle.get("valuation_rate"), 175)

        # Check valuation rate of repacked item after back-dated entry at Stores
        finished_item_sle = frappe.db.get_value('Stock Ledger Entry', {
            "item_code": "_Test Finished Item for Reposting",
            "warehouse": "Finished Goods - _TC",
            "voucher_type": "Stock Entry",
            "voucher_no": repack.name
        }, ["incoming_rate", "valuation_rate"],
                                                as_dict=1)
        self.assertEqual(finished_item_sle.get("incoming_rate"), 790)
        self.assertEqual(finished_item_sle.get("valuation_rate"), 790)

        # Check updated rate in Repack entry
        repack.reload()
        self.assertEqual(repack.items[0].get("basic_rate"), 150)
        self.assertEqual(repack.items[1].get("basic_rate"), 750)
Exemple #50
0
    def _test_reco_sle_gle(self, valuation_method):
        set_perpetual_inventory()
        # [[qty, valuation_rate, posting_date,
        #		posting_time, expected_stock_value, bin_qty, bin_valuation]]
        input_data = [[50, 1000, "2012-12-26", "12:00"],
                      [25, 900, "2012-12-26", "12:00"],
                      ["", 1000, "2012-12-20", "12:05"],
                      [20, "", "2012-12-26", "12:05"],
                      [0, "", "2012-12-31", "12:10"]]

        for d in input_data:
            set_valuation_method("_Test Item", valuation_method)

            last_sle = get_previous_sle({
                "item_code": "_Test Item",
                "warehouse": "_Test Warehouse - _TC",
                "posting_date": d[2],
                "posting_time": d[3]
            })

            # submit stock reconciliation
            stock_reco = create_stock_reconciliation(qty=d[0],
                                                     rate=d[1],
                                                     posting_date=d[2],
                                                     posting_time=d[3])

            # check stock value
            sle = frappe.db.sql("""select * from `tabStock Ledger Entry`
				where voucher_type='Stock Reconciliation' and voucher_no=%s""",
                                stock_reco.name,
                                as_dict=1)

            qty_after_transaction = flt(d[0]) if d[0] != "" else flt(
                last_sle.get("qty_after_transaction"))

            valuation_rate = flt(d[1]) if d[1] != "" else flt(
                last_sle.get("valuation_rate"))

            if qty_after_transaction == last_sle.get("qty_after_transaction") \
             and valuation_rate == last_sle.get("valuation_rate"):
                self.assertFalse(sle)
            else:
                self.assertEqual(sle[0].qty_after_transaction,
                                 qty_after_transaction)
                self.assertEqual(sle[0].stock_value,
                                 qty_after_transaction * valuation_rate)

                # no gl entries
                self.assertTrue(
                    frappe.db.get_value(
                        "Stock Ledger Entry", {
                            "voucher_type": "Stock Reconciliation",
                            "voucher_no": stock_reco.name
                        }))
                self.assertFalse(
                    get_stock_and_account_difference(
                        ["_Test Account Stock In Hand - _TC"]))

            stock_reco.cancel()

            self.assertFalse(
                frappe.db.get_value(
                    "Stock Ledger Entry", {
                        "voucher_type": "Stock Reconciliation",
                        "voucher_no": stock_reco.name
                    }))

            self.assertFalse(
                frappe.db.get_value(
                    "GL Entry", {
                        "voucher_type": "Stock Reconciliation",
                        "voucher_no": stock_reco.name
                    }))

            set_perpetual_inventory(0)