Wednesday, July 27, 2016

Master planning

Dynamics AX master planning (MP) covers the short-term calculation of material and capacity requirements. Matching supply and demand to get net requirements of a product.
Similar to forecast scheduling batch job, here we run a master scheduling batch job that creates net requirements for an item and corresponding planned orders.

Static and Dynamic master plans
On a higher level, you can also devise a strategy of having two kinds of master plans, static and dynamic.
Master planning parameters (Master planning> Setup> Parameters) has two options for current static master plan and current dynamic master plan. Master scheduling batch job usually runs every night calculating net requirements for all items. The result of that calculation is available in the static master plan. The static plan is the master plan to be used in purchase and production management for scheduling orders. It is the default plan when accessing the planned orders forms. On the other hand, the sales department needs a plan to run simulations in order to check possible delivery dates in sales orders. Therefore, sales requires running a master scheduling simulation locally, which is for the individual item only. The result of that simulation is available in the dynamic master plan.

Three options:

1. For a basic setup, you need only static plan setup. We don't specify the dynamic plan.

2. Or use the same plan for both static and dynamic. If you enter the same master plan for the static and the dynamic master plan in the master planning parameters, you run a one master plan strategy. Planned orders of current master scheduling simulations in sales update the static plan, which is used in purchasing and production. Depending on your company's requirements, this could be useful.

3. Last option would be to use separate plans for both. In order to apply a two master plan strategy, you need to enter a different master plan for the static and the dynamic plan. When running the batch job for static master scheduling in the night, it usually copies the static plan into the dynamic plan in order to base simulations on the current static plan. In this case, simulations in sales will start applying a common data basis with purchase and production management in the morning. Simulation throughout the day do not change planned orders in the static plan, thereby avoiding problems in purchasing and production caused by planned orders changing every moment.

Important note MP uses table InventSumLogTTS to assist in its calculations. Problem is this table can grow big quickly. MS and LCS diagnostics recommend to keep this table clean. MP batch job can do this cleaning for you but it requires that your dynamic plan is set. Even if you dont use dynamic plans, it is better if you set it same as your static plan. This way each run of MP job, will delete unwanted records from this table.
See class ReqCalcScheduleItem\InsertItemSched

Master plan
First thing required is setting up master plans (Master planning > Setup > Plans > Master plans). A master plan covers multiple scenarios you want handled in your master planning.
Ticking the 'Include on-hand inventory' and 'Include inv. Transactions' boxes will ensure that any stock on hand and open purchase orders will be included in planned order calculations.
The ‘Include demand forecast’ must also be ticked to ensure that the forecast entered against each item is included in the calculations.
Reduction Principle could be set to None.
Time fences, safety times, action and futures messages will not be set at the plan level. These will be set at the Coverage group level as the Master plan settings override the coverage group.
Futures messages fast tab - used to specify if requirement date on planned order should automatically update to calculated futures date
Action messages fast tab- used to specify if requirement date on planned order should automatically update to action date
Safety margin - add 2,3 days for buffer. Cushion to help you for delays. Its going to add up on top of Coverage group safety margin. Be careful.

Master plan parameters
General tab - Define your static and dynamic plans
Planned orders tab - Find trade agreements. This is to find the vendor for an item when the primary vendor is not specified on an item. Two criteria - minimum lead time or lowest price.
Standard update - Grouping planned orders into individual purchase orders when firming them. By vendor, buyer group etc. I have done customisations to add group by warehouse (InventDimId), maybe in another post.

Time fences
What is time fences? No of days master planning should look out from today to find transaction. It can be applied on Master plan or Coverage group. As said above, Master plan will override Coverage group.
For eg, if Coverage time fence is 100 days, it will look in future 100 days for all receipts and demand to do its calculations.
Coverage is longest, firming is shortest.
Explosion - usually same as coverage. Used in production. It’s the no of days in which if it finds a planned production order it will be exploded into its net requirements. You need 10 of item a, 5 of item b etc
Capacity - used to schedule work orders, resources, resource groups.
Action, Futures - calculation on no of days requirements where you will have action and futures messages.
Freeze - no of days you are not allowed to create new planned orders
Firming - automatically firm an order after a no of days

Calendar setup
Calendars in AX are used to basically set what days I can order products and can be set at different levels Warehouse, Vendor and Item Coverage Group.
The standard logic of master planning is that Item Coverage Group overrides the Vendor which overrides the warehouse (store).
Based on this we can use this logic to set up basic calendars against the warehouse (store) showing that they can order every week day.
Then if required we can set a different calendar against the Vendor if we only order products from this Vendor on different days.
The last option is to set a calendar against individual coverage group which we can then set against an item or group of items.

Using this logic should hopefully limit the number of calendars that we need to configure and should also limit the number of coverage groups that we will need to create.

Example of a warehouse and its calendar property.

Warehouse has a default calendar with working times set; which days they can order. Assumption is for most stores this will be Monday-Friday. 

Working times

Note: if you run master scheduling and calendar days are not open, no Net requirements/Planned orders will be created against the items.

Coverage groups
Coverage groups are the main tool that master planning will use on when and how we want to order products. Essentially it allows us to configure time periods (Daily, Weekly) for how frequently we want to order individual products. The coverage group is set against an individual product (Released products > Plan > Item coverage). There is also a default property 'General coverage group' in parameters form.
There are 4 coverage codes we can define.

Requirement - this group is used to order a product only when we have demand for the product for example we have a script for the drug this will be used for expensive drug items or drug items that we don’t want to stock. Each uncovered demand drives a planned supply. Usually for high value items, you don’t want to buy too much and keep in stock

Period - you can define a coverage period eg 7 days, 30 days. How many days you want to look out from the first piece of demand and group that many days of orders together. For eg an order created on 1st for next 7 days of demand. Good thing is we are grouping 7 days of demand in one order, bad is we have 7 days of on-hand sitting on us (inventory holding costs). It is based on 1st requirement date not calendar date. 7 days does not mean it creates an order every week. It means when it finds demand it creates an order for next 7 days.
One example of a setup would be to define 3 period type coverage groups.
Daily – this group as the name suggests is products that we can order daily, this coverage group is also usually combined with a minimum stock on hand value for the product and this will basically mean we will order the product based on daily usage and the minimum stock level.
Weekly – this group as the name suggests is for products that are ordered on a weekly basis again this is also usually combined with a minimum stock on hand that can then alert the user if usage is higher than normal and the product may need to be ordered in advance.
Monthly – this group is to cover products that are ordered on a monthly basis and also will normally be combined with minimum stock level that can then alert the user if usage is higher than normal and the product may need to be ordered in advance.

Min/Max - controls the size of supply.  AX will order so much to bring you to the max level. Once inventory value falls below min, an order big enough to bring value up to the max is generated.

Manual - Net requirements are not calculated. 

Eg, a daily coverage group


Positive and Negative days (Coverage groups)


Negative days - Imagine we have a sales order for 10 items on 1st and we don’t have stock.
If negative days is 0, AX will say you can't wait for the next purchase order so creates a new planned order.
Negative days is like a window from a sales order(demand) of days when receipts in those days will satisfy this demand.
But if negative days is 20 and an existing purchase order coming in on 17th, master planning pegs that purchase order against the sales order. And does not create any new planned order. No of orders in system are reduced. The purchase order will get an action message created telling that the PO is going to be late and sales order is going to get a futures message saying that the SO is also going to be 17 days late because it has a PO pegged against it. Time to contact your vendor to speed things up!

If negative days did not cover this demand on 1st, was less than 17, a new planned order would be created.

Positive days - Opposite direction. How far back master planning will look for inventory to satisfy this demand (sales order). For ex, Sales order on 31st, positive days 15 days, will go back 15 days to find any receipt. Does not find any so creates a planned order.

Note: for master planning, on hand inventory is a receipt. People set their positive days to a large number like 999 so that on-hand inventory is always seen.

Negative days works on lead times for an item. If the lead time for an item is not covered by a PO, a planned order is created. Now different items might have different lead times. There is a property called Dynamic negative days.

Dynamic negative days
Dyn neg days = LT + Neg days

This gives more flexibility and reduces no of coverage groups as LT is based on the item you are planning.


Action and Future messages (Master plan)
Negative/Positive days are closely tied with action and future messages. An example to see action and future messages in action. One prerequisite is that master plan should have both ticked and set to appropriate no of days. If say Action message is not ticked, you will not see any Action message arrow on the purchase order.

For example, Item '000000014' with a sales order for 10 qty on 1/8/2016 and a purchase order for 10 qty on 17/8/2016. The coverage group used 'MAN' has negative days set as 20. Item has no stock.

Before running master scheduling:
After running master scheduling, the sales order gets a future message and purchase order gets an action message:

Action tab is basically saying that the purchase order should be advanced by 16 days to 1/8/2016 instead of current 17/8/2016:

Futures tab is basically saying that the sales order should be delayed by 16 days to 17/8/2016 instead of current 1/8/2016:
Master scheduling batch job
Finally we run the Master scheduling batch job (Master planning > Periodic > Master scheduling) to calculate the net requirements of finished items.
We can check the results at Released products > Plan tab > Net requirements. There are two planned orders of type master plan created.
Note: As a result of these orders, and just like forecasting, there are no transactions (InventTrans) created against the item.

Approved planned orders
Intermediate step between unprocessed planned order and a firmed purchase order.
A button on top to Approve, changes status to "Approved", means we reviewed the planned order and will firm it soon but not right away. Need some time to check something else. What approving does is, it excludes these orders from getting deleted or regenerated as master planning runs every night. If regeneration happens, your items might get different quantities etc which can be confusing.
You could also Change status to "Completed", which means we reviewed the order, but we dont want to firm it and can be deleted by the master planning batch.

Fulfil minimum
Released product > Plan > Item coverage > General > Fulfil minimum

Fulfil minimum defines when your safety stock requirement date is.

This has been a long post and maybe left more questions unanswered than answered. No doubt master planning subject is not covered much and can use few more posts. Maybe in future posts. 

Tuesday, July 26, 2016

Demand forecasting

In the next two posts, we will cover demand forecasting and master planning at a basic level.

Forecasting is a long-term estimation, required to estimate and adjust future capacities of item supply and work centers.

Forecast models
First thing needed in setting up forecasting is a forecast model (Inventory management > Setup > Forecast > Forecast models). Forecast models represent different planning scenarios.

Forecast plans
Forecast models refer to forecast plans (Master planning > Setup > Plans > Forecast plans).
You specify the forecast model on a forecast plan. A Forecast plan is the basis for calculating forecasts in forecast scheduling. You may include forecast plans in master scheduling as well. It includes time fences and safety margin. More on time fences in next post.
You may enter the current forecast plan in the master planning parameters (Master planning> Setup> Parameters). The current forecast plan is the default for displaying forecast scheduling results.

Forecasting is based on items but you can also use item allocation key, if you do not enter forecasts per item number. Item allocation keys are groups, containing a percentage for every item included in the key.
You may specify item allocation keys in the form Inventory management> Setup> Forecast> Item allocation keys. After selecting or inserting an item allocation key, you may push the button Lines to enter the item numbers that are assigned to the key together with their percentage of the group total.

Demand forecast
Next you enter Demand forecast. Go to Released products > Plan tab > Demand forecast.

One option is to manually insert lines. Select a forecast model, select the site and warehouse, date, sales quantity and sales price.

Another option could be to use demand forecasting calculated using the new functionality in AX R3. With the R3 release of 2012, a new feature was added that allows your to have the system create your statistical forecasts based on the history in the system and then allow you to adjust the forecasts through Excel before automatically uploading them back into the demand forecast tables.

Yet another option could be to write custom logic to estimate forecasts from item's sales history. For example, daily sales data could be obtained by dividing sales qty sold by the no of days that item is sold. Daily sales figure can be multiplied by the no of days in the period we will order, say 1 week. Sales data can be obtained from Inventory transactions (InventTrans or SalesLines). And a batch job to run overnight and fill up your demand forecast table (ForecastSales). This needs some thinking.

In our example, we will insert few lines manually. Create two lines with date separated by a week 1/8/2016 and 8/8/2016, quantities 10 and 6.


Forecast scheduling batch job
Next you run Forecast scheduling batch job (Master planning > Periodic > Forecast scheduling) to calculate the gross requirements of finished items. As a result of forecast scheduling, Dynamics AX creates planned purchase/production orders covering the gross requirements of forecasting. These planned orders refer to the selected forecast plan.

Note: Since forecast scheduling refers to a long-term estimation, it does not include the current inventory as well as current sales and purchase orders in the calculation.

We can check the results at Released products > Plan tab > Gross requirements.
Select the forecast plan you used on top filter. Top grid shows the warehouse and the Coverage group used. (More on coverage groups in the next post on Master planning)
Lower grid shows the lines from demand forecast and a planned order with required quantity against those quantities. If you 'View details' on the planned order number, will open the planned order created, with the demand forecast lines pegged against it.

Note: As a result of these orders, there are no transactions (InventTrans) created against the item. Also its not mandatory to run forecast scheduling batch job. You can directly run master scheduling batch job that uses your demand forecast to create planned orders. Difference is the orders would be shown under a different plan (filter in planned orders list page).


Next post we will cover the basics of master planning.

Wednesday, July 20, 2016

AX retail pull jobs not uploading

What to do when a pull job is not uploading transactions. For ex, sales transactions from POS to AX head office.

First thing to check is event viewer for any errors. If you don’t see any errors, you can try below method.

A bit of background first.

AX retail push jobs use SQL change tracking to push data from HO to store. But pull jobs use a different way, a replication counter.  Let’s see how it works for sales transactions.

In below example, RetailPOS is the store database.

Table [RetailPOS].[ax].[RETAILTRANSACTIONSALESTRANS] has a field called [REPLICATIONCOUNTERFROMORIGIN] that is an auto-incremented number, every new transactions gets a new number.
There is another table [RetailPOS].[crt].[TABLEREPLICATIONLOG] that stores a list of all tables that need to upload data. This table has a field FILTERMAX, which is a reference to the above field RETAILTRANSACTIONSALESTRANS.REPLICATIONCOUNTERFROMORIGIN
Field FILTERMAX is always up-to-date to indicate the last transaction uploaded.

That means any new transactions created in store DB will get a value in RETAILTRANSACTIONSALESTRANS.REPLICATIONCOUNTERFROMORIGIN greater than the one currently set in FILTERMAX. Every time the pull job runs, it checks REPLICATIONCOUNTERFROMORIGIN in all tables against the FILTERMAX value for those tables and sees if there are any new transactions that need to be uploaded, using the Async client service and sets the FILTERMAX values for the tables to the last uploaded value.

So one thing you could do is to force the store database into thinking that the records haven’t been uploaded to Async Server. If table [RetailPOS].[crt].[TABLEREPLICATIONLOG] was cleared out it would "reset" the store database and it would re-upload all records into a new RPF file.  This should be OK since any duplicate transactions would be ignored by AX.

If you just want to resend one specific table data, you could filter for that table's records using TableName, say ax.RetailTransactionSalesTrans and set FILTERMIN and FILTERMAX to 0.
If you run the P-job now, Async Client will package this table's entire history in a RPF file for upload.
This could be helpful if you are not sure which records are missing from HO.

But if you want to reset all of the store tables, follow these steps:
1. Stop the AsyncClient in the StoreDB machine
2. Make a backup of the store Database
3. Delete the records in the TABLEREPLICATIONLOG table.
4. Start the Async Client
5. Run the P-job


/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [ID]
      ,[UPLOADSESSIONID]
      ,[TABLENAME]
      ,[FILTERFIELDNAME]
      ,[FILTERMIN]
      ,[FILTERMAX]
  FROM [RetailPOS].[crt].[TABLEREPLICATIONLOG]





/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [BARCODE]
      ,[ITEMCORRECTEDLINE]
      ,[ITEMID]
      ,[PERIODICDISCGROUP]
      ,[PERIODICDISCTYPE]
      ,[PERIODICPERCENTAGEDISCOUNT]
      ,[PRESCRIPTIONID]
      ,[PRICE]
      ,[PRICECHANGE]
      ,[PURCHID]
      ,[QTY]
      ,[REPLICATIONCOUNTERFROMORIGIN]
      ,[STORE]
      ,[TAXAMOUNT]
      ,[TAXGROUP]
      ,[TRANSACTIONCODE]
      ,[TRANSACTIONID]
      ,[CREATEDDATETIME]
      ,[MODIFIEDDATETIME]
      ,[CREATEDTRANSACTIONID]
      ,[MODIFIEDTRANSACTIONID]
      ,[DATAAREAID]
      ,[ROWVERSION]
  FROM [RetailPOS].[ax].[RETAILTRANSACTIONSALESTRANS]




Thursday, July 14, 2016

Open pure SSRS based reports in AX client

Normally you would create SSRS reports in AX using the inbuilt reporting frameworks, for example the report data provider (RDP) framework.
https://technet.microsoft.com/en-us/library/ee873263.aspx

But in case you have pure SSRS based reports, created and hosted outside of AX and you want someway to integrate them in AX, there is no "out of the box" way around. But its not that difficult. What we can do is host the SSRS report's URL inside a web control in AX, as a SSRS report is essentially just a URL.

To begin with you need to determine your SSRS reports URL links. Some information on that.
https://msdn.microsoft.com/en-us/library/dd255286.aspx

The report URL is essentially made up of two parts.
1. The link to the SSRS report server where all reports are hosted.
For example, "http://abcServer/ReportServer"

So you could create a parameter say abcServerURL (table SRSServers) in System administration, report servers form to store this bit as it would be same for all your SSRS reports. Please note the SRSServers table is global, same value across all partitions and companies. If you dont want to have same value for all partitions you would want to store this value in a different table. If you have multiple partitions, multiple companies on the same AOS cluster, this property would be shared, which means same reports would be made available to all the users. Your BI consultant should implement a security layer to map the user ids with the exact partition/company data they should be exposed to. Otherwise you would expose other companies data to a user!

2. The part of the URL string that points to the exact report
For example, "/Pages/ReportViewer.aspx?%2fReports%2f+Sales+Report&rs:Command=Render"
We would create one form for each report and 'hardcode' this second bit of the URL inside each form.

Thus, the complete URL will be a concatenation of the two parts
"http://abcServer/ReportServer/Pages/ReportViewer.aspx?%2fReports%2f+Sales+Report&rs:Command=Render"

So you might have 10 reports, all the 10 reports will have the same first part but a different second part, pointing to the exact report.

Next you need to design the form that hosts the web control. Create a form for each report. In design, add a managedHost control.



Finally in the init method of the form, we will "create" the URL and launch it.

 public void init()  
 {  
   System.Windows.Forms.WebBrowser  ssrsBrowser;  
   SRSServers                       sRSServers;  
   str                              url;  

   super();
  
   select firstonly abcServerURL from sRSServers;  
   if (!sRSServers.abcServerURL)  
     throw error ("Report server URL not setup");  

   url = sRSServers.abcServerURL + "/Pages/ReportViewer.aspx?%2fReports%2fSales+Report&rs:Command=Render";  
   ssrsBrowser= managedhost.control();  
   ssrsBrowser.Navigate(url);  
 }  

Thats it. You have successfully managed to get pure SSRS power inside your AX. Cheers.