Feature - Reorder Advise/User Defined CSV Export Example 2 - ATP (Available to Promise)

Release Date: 08/07/2020
Version: 8.0 & up

Example 2 - ATP (Available to Promise)
ATP is a major feature in Elliott that is used to project future inventory balance. The idea of ATP is that the Qty Allocated (or what we call requirement) is a lump sum accumulated number and we have no details on when the requirements are needed. The same thing holds true for Qty on Order (or what we call replenishment) -- we do not have details on when the replenishment will come in. It is important for the planner to see the future quantity balance.

It is the ATP feature that allows Elliott to handle tasks like MRP (Material Requirement Planning.) Production planners or buyers would find ATP features extremely helpful for planning purposes. Elliott offers various ATP reports under I/M -> Reports -> Available to Promise Reports. We also provide various places where you can drill down to the ATP Inquiry function. If these features are working well for you, then you do not need to explore further here. On the other hand, if you feel there is more desirable information you would like to get out of ATP, then you should look at this example to see if it will fulfill your needs. Even though Elliott ATP data is stored in the IMATPFIL table, you can access it through Crystal Reports or the Excel Query feature, but it is very difficult for you to make sure of it through third-party tools.

Let's say we want to construct an Excel CSV spreadsheet with the following columns:
  • Item Number
  • Description
  • Product Category
  • Qty OH
  • Qty Allocated
  • Qty on Order
  • Qty Back Order
  • Project Week 1 Qty OH Balance
  • Project Week 2 Qty OH Balance
  • Project Week 3 Qty OH Balance
  • Project Week 4 Qty OH Balance
  • Project Week 8 Qty OH Balance  
  • Project Week 12 Qty OH Balance
  • Project Week 16 Qty OH Balance
  • Project Week 20 Qty OH Balance
  • Project Week 24 Qty OH Balance
The Project Week ? Qty OH Balance is calculated from ATP. For the first 4 weeks, you want to know the OH balance week by week. After that, you may only need to know on a per-4-week basis since they are further away. You can also find OH balance on a day-by-day basis if you so desire. But that is usually only for the short-term future.

You start by going to I/M -> Reports -> Reorder Advise/User Def CSV -> Maintenance -> Add. Add a spec that has not been used before, such as the following: 


We then hit Enter to go to the layout screen. Similar to Example 1, we added the Item Number, Description, Product Category, Qty OH, Allocated, On Order, Back Order to the report.  If you are not sure how to do this, please look at Example 1 for more details.  Then we stop at column type "AB," which means ATP Qty Balance of a certain date or week. See sample screen below:

At this moment, the system prompts for ATP Planning Type. There are four planning types:
  • A (Actual) refers to sales orders, printed purchase orders, or release production work orders. Any orders that will impact the item quantity allocated or on order will be in the "Actual" category.
  • F (Firm) refers to anything that will soon become an actual order, but is not yet. This includes, for example, unprinted purchase orders, PO requisitions, or unreleased production work orders.
  • P (Plan/Forecast) refers to computer-generated plan orders. If you defined a forecast in Elliott, then the forecast will be included. Plan/Forecast ATP requires ATP regen, which you may or may not use.  Not all users who use the Elliott ATP feature choose to use Plan or Forecast orders.
  • X (Firm Plan) refers to the plan orders generated by the computer that have been confirmed by the planner and which will not be subjected to recalculation the next time the ATP is regen again.
For sure, almost all ATP users will use the "Actual" ATP. Not everyone will use the more advanced features of Firm, Plan/Forecast or Firm Plan orders.  By default, when you add the "AB" column, the system assumes you only want the "Actual" ATP planning type record. If you agree, simply press Enter at the above prompt.  Now the system will prompt you for number of days.

If you enter 0, it means at the end of today. 1 means at the end of tomorrow.  We assume most of the planners do their planning at the beginning of the week. Say if on Monday, adding 4 will mean end-of-day on Friday. Tuesday or Wednesday means end-of-day Saturday or Sunday.  So we enter "4" in the prompt and enter the column heading as "W1" (Week 1).

We enter the following columns as AB, 11 Days for "W2,"  11 = 4+7. Using this logic, we finish the layout as follows:

Now we can save this report and see what the output looks like. To run it, you will go to IM -> Reports -> Reorder Advice/User Def CSV -> Print -> User defined csv export. Let's say we are only interested in product category U95. So we enter the following:  

Most of our users ignore the printing output, so we usually just print it to screen and close it. Then you will see the following prompt: CSV File Created, Would You Like to Launch it?  If you would like to see the CSV in Excel, you can simply choose "Yes" to see it. See sample screen:

The Item# and Desc are deleted to protect sensitive data. The purpose of this report is to look for items with a negative balance. For example, in the second row, the item has a negative 10 at Week 16, but positive 10 at Week 20. This means there is some timing issue. If you are running a JIT (Just-in-Time) inventory with your customers, then this would be a problem. Let's take a look at the detail data for this item. The following is an example: 

The Item#, Description, Customer and Vendor Names are wiped out to protect sensitive data. In the above example, this item becomes negative 10 on 11/27/20 (week 16 in our case). There's a PO quantity of 20 scheduled to arrive on 12/02/20. As a planner, your job is to manage your future inventory by making purchases and planning for production.  This also includes coordinating with your vendors to expedite shipments, or notifying your customers of the delay.  

It is time consuming to look at the ATP inquiry item by item to find the negative amount, so this sample report allows you to review all items at the same time. It is common to add daily columns when it comes closer to delivery to see finite details and use broader periods per column for the distant future.  It is possible you will end up with many period columns within an ATP report. One of the strengths of Excel is that you can see all of them on the same spreadsheet in the most compact format.

To make the negative quantity easy to see, you can right click on the quantity areas and change the cell format to show negative amounts in red. See sample screen below:



Now the planner can clearly see those red cells and investigate the details further.

In this example, we've shown you how to retrieve ATP data with AB code type. We did not discuss another important code type -- "AN" (ATP Net Change.)  In many ways, AN is more advanced (and complicated to use) than the AB code type. It will give you more details of ATP changes between a period. We are not going to discuss "AN" code type here and suggest that you explore it further on your own.

For more information on Reorder Advise/User Defined CSV Export, please refer to the introduction and other related articles:  

EMK

Feedback and Knowledge Base