For my example, I assume "today's date" date to be 2010/08/10. Let me quickly layout the logic, again, as we reconsidered and changed it a little. This does not relate to "Calculate without historical consumptions of products", this can be left as is.
1.) We always try to look one year into the past. There is no need for a changeable from_date and to_date. From date is always today's date minus 1 year (i.e. 2009/08/10), to_date is always today's date (i.e. 2010/08/10). Initialize these variables internally and remove the date fields from the wizard. For our purposes, it is enough to calculate with consumptions between the from_date and to_date.
2.) We do not calculate these values neither for single products nor a list of products by brand name or something similar. We always need to calculate this for all products of a certain supplier. So the wizard should be moved from the product context to the supplier (and only there, not all partner types!) context. We can then gather a list of products for which the calculations shall take place by filtering the product_supplierinfo table by the supplier's id.
2.1) We then need to filter out products that do NOT match the following criteria:
- Product Active = True
- Product Type = Stockable Product
- Procure Method = Make to Stock
- Supply Method = Buy
- Can be purchased = True
- State not in ("End of Lifecycle", "Obsolete")
If the "Compute Procurements Only" wizard only takes products with the above criteria into account this filtering is not essential but might improve the overall performance of this wizard.
3.) Next, we need to calculate the average daily consumption (a.d.c.) of the product. Determine the date of the very first stock input for this product. Let's call it "first_input". Now there are three possibilities:
3.1) first_input <= from_date. In this case, the a.d.c. calculates as (consumption between from_date and to_date - refunds between from_date and to_date + open sales orders [state = "In Progress"] - open purchase orders [state = "Approved"]) / (to_date - from_date).
3.2) from_date < first_input < to_date. In this case, the a.d.c. calculates as (consumption between first_date and to_date - refunds between first_date and to_date + open sales orders - open purchase orders) / (to_date - first_date)
3.1) and 3.2) can be combined into a single formula by calculating a.d.c. as (consumption between max(from_date, first_date) and to_date - refunds between max(from_date, first_date) and to_date + open sales orders - open purchase orders) / (to_date - max(from_date, first_date))
3.3) There never was any stock input yet. In this case, the a.d.c. calculates as simply (sum of open sales order - open purchase orders) / (to_date - [date of first open purchase order]). If there are no sales orders or no purchase orders simply skip this product.
4.) Now, we calculate product_min_qty as (a.d.c.) * (supplier_lead_time + purchase_lead_time) * seasonal_factor
5.) For product_max_qty we use two different factors. For products from Asia, product_max_qty = 2 * product_min_qty but for other products, product_max_qty = 1.5 * product_min_qty. We need a new field "Max. Qty. Factor" where the purchasing agent can fill in his respective factor.
We don't need both "Calculate with historical consumptions of product" and "Calculate with historical consumptions of product and Open sales orders". Change it to two selections: "Calculate with planning values" and "Calculate with historical values".
We have query regarding this.
3.3) There never was any stock input yet. In this case, the a.d.c. calculates as simply (sum of open sales order - open purchase orders) / (to_date - [date of first open purchase order]). If there are no sales orders or no purchase orders simply skip this product.
Here open Sale/Purchase order means draft or In Progress?
If we will consider InProgress Sale order and Purchase order then those SO/PO have picking list attached.
Because when we Open Sale or Purchase order it will create picking for it.
So this case creates conflicts. If you mean draft SO and PO then its fine.