Purchasing Schedule

The migration of Adempiere modules to iDempiere

Purchasing Schedule

Postby red1 » Fri Aug 07, 2015 12:23 am

The purpose of this plugin is to generate ‘Purchase Schedule from a Sales Forecast’ as requested here http://groups.google.com/forum/#!topic/ ... tbBCz5daf4

Design Idea
i. Since there already is a Sales Forecast table, adding an isSOTrx gives it the Purchasing Forecast property. There probably be some more fields needed which we can review later. The idea here is to reuse, minimal maintenance and design consistency.
ii. Reuse will mean the possibility to adopt any present related models, processes, print format as well as understand similar issues in design. It can even enhance back the present Sales Forecast model.
iii. For example Forecast is managed and possibly generated by the Libero Manufacturing module and not from the Budget Module.
In Summary the Data Model is
i. M_Forecast + isSOTrx (no longer needed with the introduction of a new tab below.)
ii. M_ForecastLine
iii. M_ForecastProcess

Definition of function
A. Generate Purchasing Forecast from:
1. Sales Orders
2. Sales Forecast (bypassing Libero Mfg)
3. Budget Rules
3.1 Not accounting dimension
3.2 Product and Qty populated
4. StockAvailable and Replenishment rules determine product qty in purchasing line. (see next note.)

B. InfoWindow 'Forecast Generate Orders' that allows selective process for PO/SO generation
i.e. FROM M_ForecastLine WHERE !isSOTrx —> Generate PO
red1
Site Admin
 
Posts: 2759
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Purchasing Schedule

Postby zeeshan » Fri Aug 07, 2015 12:58 pm

Purchasing schedule should consider current stock as well as open purchase orders which have not yet been received, and compare this to schedule of goods required to fulfill sales forecast..
Proper scheduling requires that all suppliers have an "expected lead time" specified, which is the time lag in days between purchase order and receiving material. For example, locally purchased items typically have a short lead time whereas imported items have a long lead time.
The same item may have different lead times from different suppliers. Different purchase orders can also have different lead times from the same supplier (sometimes a local supplier may be supplying from local stock with a short lead time; sometimes they may have exhausted their local stock and have to wait for import of a new batch with a longer lead time).
Hope this is helpful.
zeeshan
 
Posts: 6
Joined: Wed Mar 23, 2011 6:37 pm

Re: Purchasing Schedule

Postby red1 » Sat Aug 08, 2015 1:00 am

The ‘stock available’ qty is good to be accounted for but will require more thought as to the model for availability flow over a calendar, predicted from prevalent replenishment rules /or Sales vs Purchases forecasts.
‘Lead time’ can be based on the Product Purchasing tab (as quite well put) present properties of DeliveryTimePromised/Actual. (Zeeshan's point is solved by considering the transaction doc's later promised date if true.)

I been thinking the whole design over, and considering if the ‘Replenishment’ rules can be now linked to this forecasting engine, where it is then updated from the analysis, and made as integral part of the Purchasing Forecast. This can then be also called, 'Dynamic Replenishment System'.

Replenishment happens due to stock falling below reorder qty. Now you can anticipate replenishment as sales forecast schedule affect stock, the purchasing forecast will set the qty needed from replenishment rules. With that the forecasted Purchase Orders will have its required quantities.

Dynamic replenishment can occur when the reorder or replenishment orders overlap too often, or the sold qty over a lead time or promised delivery time surpasses the fixed qty to order. Replenishment generating is thus redundant and in its place is the purchasing forecast that is more dynamic and intelligent.
red1
Site Admin
 
Posts: 2759
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Q

Postby red1 » Tue Aug 18, 2015 1:41 pm

I have pushed source and 2Pack to http://bitbucket.org/red1/org.purchasing.forecast as preliminary release, awaiting testing next. You can also fetch the 2Pack directly to examine its model - PurchaseForecastWindow.zip
The plugin will manage the creation of Forecast model with new tab to indicate if is active. The purchasing forecast thus uses this model and the event validator in the plugin will create it from either a Sales Forecast or Sales Order activity. The plugin in theory checks for lead time in its Product Purchasing vendor's detail and set a promised date. It will check other open POs to adjust its actual Qty to buy.
The 2Pack creates the new process tab and assign it as separate new window instead of within Libero Mfg, for general basic use.
After confirmed testing, a migration script will be issued. Then some Info Window and print formats to give more utility features to the whole model are next.
red1
Site Admin
 
Posts: 2759
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Purchasing Schedule

Postby red1 » Tue Aug 18, 2015 4:56 pm

The Forecast Model
I added a new tab to the Forecast window, Forecast Process as a placeholder to indicate that a Purchasing forecast is been made. Such a design that does not disturb the main tabs, is to prevent any impact, and ensure backward compatibility. The screenshot below is at the ForecastLine tab with the ForecastProcess tab in the bottom pane. It is Read-Only and at the moment has no record as nothing is been generated yet. A successful test will be done later. Now is just explaining the rationale.

Screen Shot 2015-08-18 at 2.38.46 PM.png
Screen Shot 2015-08-18 at 2.38.46 PM.png (78.78 KiB) Viewed 12665 times
It contains reference to which Sales Order or Sales Forecast it comes from. The role of the Forecast model is to keep track of the product and qty and promised date. It will be later used to generate an actual Purchase Order. Bear in mind that only when there is insufficient stock predicted that this forecast comes into play.

The tricky part is just those two values :- Promised Date and Qty, where they are also directly resolved in the following:
1. Promised Date is the time or date the product is needed. That is easily determined by the Product Purchasing tab which its assigned vendor DeliveryTime is set. So if a sales is promised to deliver say in 30 days, and that product needs to be purchased and its vendor needs 10 days, the purchase order has to be issued in 20 days time. (However to consider Zeeshan's point earlier of vendor's possible differing, we check if the document (Sales Order or Forecast) has set a later promised date which is then taken instead.)
2. Qty is calculated from the stock in hand where if fully available, there will be no schedule or purchasing needed, and when partially fulfilled, its balance is required. This is a bit more complicated but is easily ascertained by taking the stock in hand at the promised date, less the confirmed sales or reservation, adding the confirmed purchases or ordered within the time frame. All promised reservation and orders after the promised date are not considered.
red1
Site Admin
 
Posts: 2759
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Purchasing Schedule

Postby red1 » Thu Aug 20, 2015 5:07 pm

Non Impact to Others
New model has no impact to the core of iDempiere ERP model and processes nor any other 3rd party plugin such as Libero MFG.
The plugin when activated will check three documents’s events:
1. Sales Orders, where each order will generate a corresponding new Forecast record.

2. From Forecast (Sales) creation. It will not be recursive because it will check any forecast record not to have a process (only Sales Forecast)
(Of course Libero MFG handles Forecast events but this is how Libero does it:
a. Libero create Forecasts via Import Product Planning Data or via Calculate Forecast which is a blank beanshell! So it is only via a direct creation manually which can rightfully trigger the Purchasing Forecast directly then. That is not impacting back the Sales Forecast.
b. When a Forecast value is changed, Libero checks its MRP record that has such forecast detail and update accordingly. Again, it will not impact non MRP records, as PO forecast is not an MRP record, both directions are safe as for now.)

3. From Budget Plan (TODO pending) where new budget plan line will trigger the same as 1 and 2 above. Just that the PromisedDate will be related to a Period value. The plan details are those that are not accounting element and thus meant for Sales target and Purchase budgeting. Either aspect can be transfered accordingly - a sales target will generate a PO forecast (may check that it is not redundant to a purchasing budget) and a purchasing budget plan detail is more straight from forecast.

3.2 There can be a reverse option to generate Budget Purchasing Plan from such a Purchasing Forecast. By placing purchasing schedule into the Budget Plan has an added role of monitoring a purchasing budget proactively instead of generating POs from the forecast schedule on exact dates. Purchasing budget is according to monthly periods. Any purchase will then be tracked by the budget and forewarned if it exceeds the figures.
red1
Site Admin
 
Posts: 2759
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Purchasing Schedule

Postby red1 » Thu Sep 03, 2015 4:20 pm

Screen Shot 2015-09-03 at 10.49.16 AM.png
Screen Shot 2015-09-03 at 10.49.16 AM.png (14.33 KiB) Viewed 12591 times
The menu contains following:
1) Generate Purchase Forecast to do bulk conversion from Sales Forecasts
2) Generate Purchases to do bulk POs from (1)
3) Purchasing Schedule, an InfoWindow which can selectively process (2)
4) Forecast Model, to manage manually both sales and purchasing forecasts with the new process tab.
Item (3) has been tested OK for Sales Order origin, which also tested the ModelValidator. Next will be to test the other cases and suggest suitable params for (1) and (2).
Below is item (3) in action.

Screen Shot 2015-09-03 at 9.04.08 AM.png
Screen Shot 2015-09-03 at 9.04.08 AM.png (70.15 KiB) Viewed 12591 times
One thing to note is that generating PO is more complex than you think, requiring a Sales or Forecast to be consolidated to respective individual vendors that supply each detail line product. And the better script to adopt is from public class ProjectGenPO rather than public class OrderPOCreate.
red1
Site Admin
 
Posts: 2759
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Purchasing Schedule

Postby red1 » Sun Sep 06, 2015 1:25 pm

The above is done and committed to my bitbucket. I am also planning to add the following:
1. During final PO generation via InfoWindow, add criteria:
a. Vendor choice (besides Period, Warehouse and Product)
b. Substitute Product (when main lead time is longer)

2. During final PO generation via bulk process, add criteria:
a. Today
b. Days in advance (this issue POs that are due later)

The last item leads us to the confusion about lead days and The Promised Date. I am not sure if I have got it wrong. The question is, 'Is the Promised Date in a Sales Order the same as its related Purchase Order?'
My present assumption is lets say, it is Yes.
Therefore during PO generation, the PromisedDate is copied exactly over and the lead time is calculated as PO's OrderedDate.
Then the next question is about Zeeshan's condition. How do we interrupt that order date? Do we allow it to be earlier or later accordingly? Another question will be, 'What if we run out of lead time? I.e. say there is 10 days lead time required and the PromisedDate is 9 days away?
I will think about this for a moment and get back here.

p/s - I am also thinking of the Substitute Product option here as i find it not used much in the code, in fact not at all except displayed in the Product InfoWindow elsewhere for selection. This criteria option will allow its more direct use. But question is, how and what governs their use?
red1
Site Admin
 
Posts: 2759
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Purchasing Schedule

Postby zeeshan » Mon Sep 07, 2015 5:45 pm

As I understand it, usually delivery date of purchase order created for sales forecast should arrive at least 1 day earlier than required for sales forecast. Otherwise there may not be time to run the production that day as per the sales forecast.
However, keeping these two dates the same can also work. Purchase dept can arrange lag time required for production by adjusting lead time for each supplier.
zeeshan
 
Posts: 6
Joined: Wed Mar 23, 2011 6:37 pm

Re: Purchasing Schedule

Postby red1 » Thu Sep 10, 2015 6:06 am

Resolved LeadTime easily by allowing a 'before range date' selection to filter tentative purchases order dates. Lead Time is date calculated by subracting from Promised Date based on rule described earlier. Further columns added as stated above such as choice by vendor. Now tested 2Pack clean plugin install working for all processes. All source in http://bitbucket.org/red1/org.purchasing.forecast and a binary plugin with 2Pack and separate ForecastWindow 2Pack option uploaded to http://sourceforge.net/projects/red1/up ... eForecast/

PluginSuccess.png
PluginSuccess.png (34.71 KiB) Viewed 12493 times
Plugin when run successfully. Embedded 2Pack will also PackIn the reference key for ForecastSalesLine, validation of BP Vendor, InfoWindow with process and whole menu. If preferred, use the Pack In to manually import ForecastWindow.zip.

PackInForecast.png
PackInForecast.png (44.5 KiB) Viewed 12493 times
You can then look at the menu given before and run the first process to bulk convert Sales Forecasts (which can also be created in the Forecast Model window).

GeneratePOForecastBulk.png
GeneratePOForecastBulk.png (27.5 KiB) Viewed 12493 times
When run again second time will be safe and not repeat any processed ones.

GenerateBulkForecast2.png
GenerateBulkForecast2.png (27.4 KiB) Viewed 12493 times
During InfoWindow selective PO generation, related doc event information is given. In screenshot example below, the 3 selected ones are cleared from panel when pop up shows 2 POs are generated successfully from the forecast selection. POs are consolidated to respective vendors. The criteria panel now have choice of filtering to particular vendor besides Period, Lead Time (before or equal range the PO forecasts lead order dates fall), Warehouse Locator as well as by Product. (Remember that POs can also be bulk generated instead without criteria (2nd item in menu).

SelectiveForecast2PO.png
SelectiveForecast2PO.png (58.07 KiB) Viewed 12493 times
We can also easily cross examine any processed records by checking the Processed box and refresh the InfoWindow. POLine reference is given in the last column. All this ensure higher visibility to user action. Based on InfoWindow they are easily configurable (documentation later will illustrate all).

PurchasingForecastProcessed2.png
PurchasingForecastProcessed2.png (113.11 KiB) Viewed 12493 times
Next will be to refine more changes during/with FitNesse testing and a developer/user manual PDF.
red1
Site Admin
 
Posts: 2759
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Purchasing Schedule

Postby red1 » Sat Sep 12, 2015 2:22 am

To make life easy for everybody including myself, I am now making standard fare in all projects to include the Pack Out tempate in the 2Pack so that after Pack In, such a Pack Out will show up for reuse.

PackOutForecastElements.png
PackOutForecastElements.png (62.77 KiB) Viewed 12463 times
I updated all source and binary and 2Pack online accordingly. The way to pack out this is simply add in the Pack Out, an item, type Data with this script:
Code: Select all
SELECT * FROM AD_Package_Exp WHERE NAME = '<your pack-out name>'; AD_Package_Exp_Detail
In my case, the Name is 'ForecastWindow'. As seen above it gets embedded always for successive PackIn/Outs. No more hassle of defining ever again whenever a change is made to recreate a 2Pack.
red1
Site Admin
 
Posts: 2759
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Purchasing Schedule

Postby red1 » Wed Sep 23, 2015 4:50 am

Starting on documentation now, with a final part to make an InfoWindow where Budget Plan Sales Details are selected to generate Sales Forecasts. And FitNesse testing for each part of the process.

Screen Shot 2015-09-22 at 4.50.15 PM.png
Screen Shot 2015-09-22 at 4.50.15 PM.png (83.76 KiB) Viewed 12355 times
red1
Site Admin
 
Posts: 2759
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Purchasing Schedule

Postby red1 » Mon Oct 05, 2015 11:38 pm

Screen Shot 2015-10-04 at 7.37.08 PM.png
Screen Shot 2015-10-04 at 7.37.08 PM.png (28.58 KiB) Viewed 12186 times
The documentation is incorporated into http://red1.org/iDempiereFitNesse.pdf pages 25 -39. All binaries are updated and committed to SourceForge.net for Budget https://sourceforge.net/projects/red1/files/p2/Budget/
and Purchasing Forecast http://sourceforge.net/projects/red1/fi ... eForecast/
Source code is pushed to http://bitbucket.org/red1/

Testing regime is at http://sourceforge.net/projects/red1/files/Testing/
1. Take the fitnessefixture.jar plugin to install via your OSGi console and stop the previous one.
2. Take the FitNesseRoot to replace in your same for testing.
Below are some of the testing screenshots in order of operations:

Screen Shot 2015-10-04 at 2.14.13 PM.png
Screen Shot 2015-10-04 at 2.14.13 PM.png (105.24 KiB) Viewed 12186 times
Screen Shot 2015-10-04 at 2.13.33 PM.png
Screen Shot 2015-10-04 at 2.13.33 PM.png (29.24 KiB) Viewed 12186 times
Screen Shot 2015-10-04 at 2.13.17 PM.png
Screen Shot 2015-10-04 at 2.13.17 PM.png (69.15 KiB) Viewed 12186 times
red1
Site Admin
 
Posts: 2759
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia


Return to iDempiere

Who is online

Users browsing this forum: No registered users and 3 guests

cron