Try ERPNext Buy Support Partners Foundation Foundation Members

Just in time. Change default DocType Bin to display all future stock movements for each article?

Idea is to see only future transactions (incoming and outgoing) which change article stock quantity. It is used to detect when stock quantity drops below 0 to plan purchasing according to this date.

Example of the result

To achieve that we currently added code to Bin DocType erpnext/stock/doctype/bin/bin.py

Function def set_projected_qty(self):

Added code is something like

    self.items_list = []
	
	productionOrdersMaterial = frappe.db.sql('''select pro.name, pro.planned_start_date, item.required_qty
		from `tabProduction Order` pro, `tabProduction Order Item` item
		where
			item.item_code = %s and
			item.parent = pro.name and
			item.source_warehouse = %s and
			pro.status not in ("Draft","Stopped","Completed","Cancelled")
		order by pro.planned_start_date''', (self.item_code, self.warehouse))
	
	#add material to production order to Bin
	for line in productionOrdersMaterial:
		date = datetime.datetime(line[1].year, line[1].month, line[1].day).date()

		doc = frappe.get_doc({
			"doctype":"Bin Item",
			"parent":self.name,
			"parentfield":"items_list",
			"parenttype":"Bin",
			"date":date,
			"item":self.item_code,
			"qty":line[2]*-1,
			"doc_type":"Production Order",
			"linked_doc":line[0]
		})
		self.items_list.append(doc)

#More SQL queries here to find all future changes

#sort the list by date and positive numbers before negative so incoming goods would take care of the needs on the same day
		self.items_list.sort(key=attrgetter('qty'), reverse=True)
		self.items_list.sort(key=attrgetter('date'))
		#sum the sums...
		lineSum = self.actual_qty
		for item in self.items_list:
			lineSum = lineSum + item.qty
			item.sum = lineSum

Bin Item is Child table created in custom app which is imported when custom app is installed (fixtures info is used to add Bin Item to Bin form when custom app is installed)

If I understood correctly then child table cannot populate itself and child table has no reference or knowledge of the parent. So we cannot implement controller in custom app DocType Bin Item.

Here Adding custom behaviour to default ERPNext apps is a suggestion that we should make our code generic to get it to release version to survive the ERPNext updates.

We would like to start with feature to display all future stock movements for each article in BIN to order components just in time. This initial solution only shows how article quantity at one stock location changes. (We want to check each location separately as one location is subcontractor 1 and another is subcontractor 2 so if we want to produce at subcontractor 1 we want to order components to their warehouse just in time not elsewhere)

In future we want to see what we need to order today to get components to right subcontractor warehouse just in time. So system would use suppliers delivery time and will suggest how much we need to order today. Based on that purchase orders can be created.

We need this because if we plan to produce something each month for whole year we do not want to order all components for whole year at the same time.

When some document or action changes article quantity now or in future Bin Item s list needs updating.
Running multiple SQL queries in bin.py can cause performance issues and some users may not want this display. (Default off, can be switched on from Stock Settings?)
Could we get SQL queries and Bin Items display to generic ERP code or this implementation is not inline with ERPNext best practices?

Or should we consider creating generic reports?
Or we could add link to Bin entry to open custom app page which executes and displays future stock movements?

Can anyone share their knowledge? Or we need just develop something and make the pull request and wait what happens?

All comments are welcome!