MRP Recommendation Fields in the mrmrprm table

March 12, 2014

This is frequently asked by folks running MRP and trying to write their own reports.
The question is what do the various single character codes in various fields really mean?

Field = “kind”:

So for each Raise New WO there will be 10 R records if there are 10 lines on the made product’s BoM.
If you are doing an MRP Analysis type reports you will always ignore the Rs.
But they are very useful for other types of reports.
For instance the slect REP pack has PFS and MRPSummary reports which both use the R records to show the demand qty and date that would affect the PFS balance if the suggested WO had been raised. http://sl-ect.co.uk/rep.html

Hence an interesting thing to consider: You don’t always need to raise WO too far out because MRP can generate the same info for reporting on what to buy. If you don’t raise too many actual WO you get fewer recommendations to alter them and more to Raise New on the next MRP run. These just follow changes to the forecast or sales orders, rather than having to change WO dates and quantities if the top level demand changes.

Field = “acted”:

Field = “tag_type”:

What’s new with Preactor and Sage?

September 24, 2013

Preactor V12 is now available, the main features being:

Additionally V12 saw the release of a significant new product in the Preactor range. P400 GMPS is a Master Production Schedule that can suggest production quantities for top level SKUs in order to keep future stock at an acceptable level. The level can be a simple minimum stock or can be expressed in terms of days of cover. The calculation takes into account the sales forecast and EOQ batch sizes. In the background the traditional focus of Preactor comes into play with capacity management based on the suggested make quantities. So it is also a rough cut capacity planner. The suggested plan will typically then be fed into a MRP process for lower BoM level requirements, which can then be fed into a standard Preactor APS system for finite capacity scheduling. Perfect for FMCG companies! http://sl-ect.co.uk/PreactorGMPS.html

slect PRO System:

New from 2013 is the ability to create “Preactor Orders” (PRO) to represent production that you want to make.

Previously you had to create Works Orders in Sage and import to Preactor to get a schedule for a new order. The idea behind PRO is that when you have new customer demand we’ll let you quickly create PRO orders directly in Preactor, fetching the BoM and Routing on the fly from Sage.

So that you can speed up the time it takes to get back to the customer with an accurate promise date.

A great new feature is that PRO will look down all levels of BoM for the product entered. When it finds “Make” items with shortages it will generate PRO at this level, continuing down until all BoM items have been dealt with.

“Buy” items that can’t be satisfied from stock or due in purchase orders will generate “PSUG” purchase suggestions on today + supplier lead time, in order to dynamically calculate the earliest start date for the PRO orders.

Then you can convert the PRO to WO as part of an export back to Sage (requires the Defacto UI module). Or just discard them if you were dealing with a speculative enquiry. The choice is yours.


The system is available for Preactor 400 and 500 APS against Sage 1000 or Line 500.

We are working on doing the same for Sage X3 ERP. A joint development would be great – get in touch if you are an end user interested in this.

Free SSRS Reports for Sage 1000 / Line 500

September 24, 2013

Everyone gives stuff away for free on the internet now, right?

We were feeling left out, so we've made some SSRS reports from the "REP" pack available for download here:


Format is .RDL files, ready for use in your report server.

Make To Order – how to get a fast, accurate promise date for your customer.

April 2, 2013

It’s a common problem …. you are a manufacturer taking in sales orders from customers. The order may have 5 different products with the same or different required dates.

Even better (worse!) your products are complex and there are many levels of Bill Of Materials (BoM) to look through to work out which items you need to make or buy in order to satisfy this new customer demand.

With some ERP systems you may have to manually check all supply and demand, perhaps for dozens of part numbers.

The customer wants an answer quickly “when can we expect delivery?”.

So you don’t want to keeep them waiting. But you don’t want to give a quick answer that is not accurate. Either way you could lose this and / or future business.

The answer is to have a production scheduling system that can read your ERP system to fetch BoM and process routes and effectively run an MRP calculation down all levels, suggesting new Works Orders (WO) or Purchase Orders (PO) along the way. If it finds spare stock on hand or due in at any level then that quantity is reserved and no new requirements suggested.

The PO suggestions then tell you the ealiest date that each WO could start, based on the supplier purchasing lead time.

The WO can then be “forward scheduled” into the existing factory plan to find the earliest finish date for each line on the sales order or quotation.

That production scheduling system could be from Preactor International. A Preactor APS system has the ability to do all of the above in a much shorter time than it will take you to trawl through the ERP data manually.

And the answer will be more accurate beacause it will be based on your existing achievable factory plan. Customer delivery performance will improve, as will your reputation at the customer!

More info? Check out some of the screenshots on this PDF: http://sl-ect.co.uk/Documents/preactor-sage1000-std.pdf

Posted on August 5, 2010 by officeslect

14 Things You Can Do With Sage ERP 1000 / Line 500

1) View all the main Full Stock Details and Stock Status fields on one page.

2) Show the TIME of a transaction in Stock History.

3) Find all the products that are expected to have stock below zero (or the ROL) at some point in the future.

4) Use an MRP recommendations report that can be edited in Excel, with a drill down to Predict Future Stock.

5) View what you have spent with a supplier by year / period.

6) List which data tables were added to by doing a transaction - to know where to look for the data.

7) Track who has changed a standard cost, when they did it, and the before and after values to find the change in stock value.

8) Report on all changes and additions to Sales Order lines.

9) Report on all changes and additions to Purchase Order lines.

10) Explode a BOM to all levels and view this in Excel.

11) Enter a top level (sales) product and quantity, then explode the BOM to find the longest cumulative lead time

and check stock availability. From which you can estimate a promise date for a new sales order.

12) Force WO Completions to take component stock ONLY from specified "backflush bins".

13) Automate the entry of timesheets to get real time labour hours and WIP Tracking data.

14) Stop all those "Bring PO forward" MRP recommendations by driving the MRP from an achievable production plan.

Preactor Version 11 is now available!

Posted on June 23, 2010 by officeslect

Including the all new Material Explorer.

Tip: Add notes to a PO after calling the supplier

Posted on June 23, 2010 by officeslect

If you need to put a note (eg. latest info from the supplier) against a PO line, use the “F3-Text” button to get 5 text lines.

Preactor Case Study for Universal Products is now available!

Posted on April 26, 2010 by officeslect

The new case study for Preactor at Universal Products Ltd can be found here:


Take a look!

Forward versus Backward scheduling – What’s the difference?

Posted on April 26, 2010 by officeslect

Forward Scheduling

A Preactor system will NOT plan jobs in the past. So a forward schedule starts at TODAY and takes each job by priority and puts it on the planning board in the first available slot. Depending on the availability of machines due shift patterns and other more important jobs already planned on the required machine.

So a forward plan tells you:

The resulting schedule Gantt chart will show jobs bunched up to the left hand side of the timeline.

So it tends to increase WIP. And makes it difficult to get sensible promise dates for new orders because just adding them onto the schedule could result in the new order being late and an order placed weeks ago being very early. Maybe the orders have the same required date and BOTH could be on time if the whole system is re-planned!

Backward Scheduling

The Sage 1000 MRP program is a backward schedule!

It works back from the top level required date on a Sales order or forecast, calculating start / end dates for each level of BOM, using the lead times.

Often this suggests start dates that are in the past, even though the MRP program has no restriction on machine / people capacity.

With a Preactor finite capacity schedule this could be even “worse”. Preactor only lets one job be planned on each machine at a time, so if there is a long bottleneck on a resource then it too will try to plan things in the past. But Preactor deals with this in a different way to MRP, because it refuses to schedule the operations that would need to run before today on a backward schedule.

Leaving the planner to do something about them.

The resulting schedule Gantt chart will show jobs more to the right hand side of the timeline, and you will be able to see gaps in the middle where new orders can be placed without affecting the expected dates of existing orders.

A Good Compromise

Just one idea, there are others:

If the ops were EARLY on a FORWARD, they are bound to still FIT on a BACKWARD, without going into the past!

The end result being a plan where urgent or potentially late orders are planned to run ASAP, the rest are planned to finish when they are needed.

So they don’t start until they really need to , so WIP is reduced and MRP for purchasing can be set to only bring material in on dates where it is actually needed.

Reducing stock holding costs and freeing up cash and warehouse space!

The plan will also show gaps where new orders can be added.

Week Number for pivot tables on Sage ERP data

Posted on April 10, 2010 by officeslect

Here’s how to how to convert any date into a week number.

For example, you could create a SQL View on the stkhstm table and then convert the movement date field to a week number.

Then pull the data into an Excel pivot table for instant totalling of quantity or value with week numbers going across the page.

Download a SQL Function slectrep_ISOweek.sql from here: http://temp.sl-ectcouk.officelive.com/downloads.aspx

Then put this into your SQL code: LEFT(scheme.slectrep_ISOweek(scheme.stkhstm.dated), 7) AS yy_wk

Uses the ISO system so it should get it right even if 30th December this year is actually week 1 of next year!

We use it in our Predict Future Stock screen and other REP reports: http://temp.sl-ectcouk.officelive.com/REP.aspx

Reporting Services or Qlikview???

Posted on April 10, 2010 by officeslect

Which is the better tool for Sage ERP 1000 companies? Qlikview or SQL Server Reporting Services (SSRS) ?

We have a report pack for Sage using SSRS (Download more info here: http://temp.sl-ectcouk.officelive.com/REP.aspx )

Should we convert to Qlikview or maintain 2 sets of reports to support both bits of software?

Has anyone used both tools and can offer some pros and cons?

Sage Line 500 User Login Name

Posted on March 16, 2010 by officeslect

Problem: There is no field in users table (usermastm) to hold a name.

Solution: But the “fast menu” field is obsolete now, so use that for a person’s name and see it in the F2 browse!

RE: Master Production Schedule anybody?

Posted on March 8, 2010 by officeslect

Just posted this question on the Sage User Network forum, so I’ll ask it here too……

I’m trying to find some good 3rd party MPS software. Maybe it would create the sales forecast from history and sales person knowledge and promotional activity. But more importantly it needs to turn this into a MPS, taking into account the current stocks, production plans and firm sales orders. There should be an easy to use interface (we’re competing with Excel here!) where the planner can modify the (system) calculated production plan as he/she sees fit. This should then be able to be published as an Available To Promise (ATP) enquiry for sales dept to use. The final MPS would also be pushed into Sage to drive MRP for the lower BOM levels. And it should be able to accept back a true forward scheduled plan (maybe from Preactor) so that the dates used in the ATP and the next MPS represent a realistic capacity constrained plan of what you will be making in the short term.
As you might imagine, this is very suitable for FMCG companies. Which reminds me – it should be able to cope with minimum stock levels expressed in terms of "days cover" rather than just a fixed stock quantity.
Anyone know of such a thing??