Feature - Reorder Advise/User Defined CSV Export Introduction

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

Introduction 
The User-Defined CSV Export is a specialized report writing tool and CSV creator for inventory information to allow you to analyze an Excel spreadsheet. This program allows access to item-related information that is difficult, if not impossible, to access with general report writing tools like Crystal Reports or other third-party analysis tools. For this reason, we also call this feature "Elliott Inventory Report Writer." The type of data that can be accessed includes, but is not limited to, the following:
  • ATP (Available to Promise) data (for inventory or production planners)
  • Item 12-month history data - including quantity sold, sales amount, cost, usage...etc.
  • Item quantity sold data by customer or customer type
  • Item Link information (to determine if a link for an item has been set up or not)
  • Item Attribute data
These data are difficult for generic report writers to access. You can continue using your generic report writing tools if they fit your purpose. Look at the "Elliott Inventory Report Writer" feature if something is more desirable that you can't get through your existing report writing tools. 

Even though this feature will output a traditional Elliott report, generally speaking, the user does not look at the Elliott report output in this case and only focuses on the CSV file output in Excel. The output format can be one item per line, or one item/location per line depending on if you include the "Location" column on the layout.

The layout of this "Elliott Inventory Report Writer" is totally user-definable where you decide what columns to include. This function was introduced with Elliott 8.2. Many features have been added over time. If you do not see a particular feature described in this document, then you should just update your Elliott to the latest revision.

Example 1 - Item Quantity & History

We believe the best way for you to understand this feature is to give you examples so you can follow our concept step by step. The first example shows you how to create a CSV spreadsheet that has item quantity on hand, allocated, on order, back order, quantity available (formula), excess quantity (formula), and history data of the quantity sold for the four quarters of last year and this year. See this KB article for more details: http://support.elliott.com/knowledgebase/articles/1952665-feature-reorder-advise-user-defined-csv-export-e

Example 2 - ATP (Available to Promise)

We then give you another example of how to retrieve ATP (Available to Promise) data.  ATP is a feature great for planners and buyers. The ATP data is stored in the IMATPFIL table. But the chances of successfully retrieving it in a useful format are very low, if not impossible.  See this KB article for more details: http://support.elliott.com/knowledgebase/articles/1952722-feature-reorder-advise-user-defined-csv-export-e

Cost Information Security

As an administrator, you may define an item's cost (average, last or standard cost) on the CSV. On the other hand, there's global user security to determine if a user can see item cost or not. If the user cannot see item cost on a report, but is allowed to run the user-defined CSV export, the cost column(w) will be suppressed automatically.

Location Selection & Netable Flag

If you did not define a location column on the CSV layout, then when you run that report, the system assumes one item per row, the location field is skipped automatically and "ALL" locations are assumed. If location is defined on the CSV layout, the system will export one item/location per row.  In the selection screen "3. Location," you can either hit Enter for "ALL" locations, or enter up to 10 specific locations.  See sample screen below:

Generally speaking, we do not include non-netable location quantity when "ALL" locations are selected. In Location file maintenance, field "13. ATP Netable Loc" determine whether a location is netable. A non-netable location may refer to a defective warehouse, service warehouse or consignment inventory warehouse...etc. where the inventory in those locations should not be counted as available to sell.

But on the other hand, if you select a specific location in "3. Location" for non-netable locations, then we will include those non-netable locations regardless. Therefore, based on how you enter values in "3. Location," the result may be inconsistent. For example, let's say we have a total of three locations: 1, 2 and 3.  Let's assume 1 and 2 are netable locations and 3 is non-netable. If you choose to run a report by specifically entering location 1, 2 and 3, you will see location 3's numbers show up.  But if you choose to run a report for ALL locations, then location 3 does not show up. This inconsistency may confuse you.

While the rule to exclude non-netable locations when entering ALL locations generally makes sense for quantity on hand, on order, allocated, back order...etc., it may not always be appropriate with history data for consignment inventory scenarios. To handle consigned inventory, we typically create a warehouse location and indicate "13. ATP Netable Loc" as "N" since those inventory items are currently stored at the customer's location and we don't want to include them as available to sell or use. On the other hand, we do want to include sales history for consignment inventory.  To solve this issue, we introduced a flag in Global Setup -> Dist -> Reordering Advise CSV Export -> screen 3 -> "1. Include Non-Netable Location in Sold Qty/Amt Column?" Default is "N" which means non-netable location sales history will not be included when you choose "ALL" locations.  But if you have this flag set to "Y," then the non-netable location sales history will be included. 

To make it easy to understand this principle, we break down quantity and amount data into two different categories: "Current" and "History."  Current refers to quantity on hand, allocated, on order,  and back order. "History" refers to quantity sold, sales amount...etc.  "History" data may be included for non-netable locations when "ALL" locations are chosen provided the Global Setup above is set to "Y." For a list of what code types are considered "Current" or "History," see the section "Reference B - Current vs. History Columns."

Admin vs. User Access

You can run and define User Defined CSV with the following path: I/M -> Reports -> Reorder Advise/User Def CSV. You may wish to let other users run the same User Defined CSV, but you may not want to let them define new, modify existing -- or worse -- delete the existing User Defined CSV template. Therefore, this path is only appropriate for the admin users.

For regular users where you allow them to run User Defined CSV, but not to modify them, you would grant them the access to I/M -> Reports -> User Defined CSV Report.

User Defined CSV Batch

The purpose of User Defined CSV Batch is to provide a pointer to a CSV file to be used by User Defined CSV Export as a pre-selected item. For example, if you have a list of items like:
ABC1
ABC2
BCD1
BCD2
....etc.
And you'd like to use these items as input and analyze them through User Defined CSV export. This can be very useful when it is not possible to use the parameters provided by User Defined CSV to narrow down to the item list. To use this feature, you can store the items list in a CSV file.  The item number does not have to be the only column in the CSV.  But it must be the first column.  Therefore, any existing CSV file with an item as the first column can be used as the input CSV file to drive User Defined CSV Export. Please see the following KB article for details: http://support.elliott.com/knowledgebase/articles/1838167-feature-user-defined-csv-export-item-batch-suppo

Performance Considerations

Depending on how you define the template of User Defined CSV, you may experience slow performance when you run User Defined CSV export. When you retrieve ATP information, the system needs to read the detail ATP records.  If you have a large ATP database, this may slow down the performance somewhat. If you retrieve sales history information, it will try to get data from the IMLOCHST table, which should have reasonable speed.  However, when you try to get "QS" (Qty Sold) information, you could specify the quantity sold for a specific customer or customer type. If that is the case, the only way the system can calculate that information is by looking up the IMINVTRX table, which tends to be a big table, and that can  slow down the performance.

You may consider running the report on the PSQL server directly which can result in maybe 10 times performance improvement compared to running it from a workstation.  If there's a security restriction for doing so, you may consider running deferred processing on a PSQL server and letting any User Defined CSV Export that takes a long time run through deferred processing.  Defer Processing supports the "run now" feature which can help to shift the burden of running reports on the client side to the server to benefit performance. 

Reference A - List of Available Code Types

The following is a list of all valid code types (fields) in User Defined CSV:
  • 1C=1st Component
  • 1D=1st Pending Receiving Date
  • 1Q=12 Pending Receiving Qty
  • 1V=1st Vessel# of Pending Recv
  • A = Age (# of Days)
  • AB=ATP Qty Balance on 999 Days
  • AC=Averge Cost
  • AD=Age on Date
  • AL=ATP Qty Bal at Lead Time
  • AN=ATP Qty Net Change
  • AO=Added on (date)
  • AP=ATP Qty Bal at LT + PlanPRd
  • AQ=Available Qty(OH - Alloc)
  • AR=Attribute
  • AS=ATP PO Trx BF Ship LT
  • AT=ATP # Trx BF Today
  • AY=Activity Code
  • BA=BOMP Avail
  • BC=Buyer Code
  • BB=Bin Number
  • BQ=Balance Qty )OH-Alloc+OO)
  • C = Cost (Period Aggregate)
  • CC=Commodity Code
  • CF=Controlled Flag
  • CI=Customer Item Number
  • CV=Constant Value
  • D1=Description 1
  • D2=Description 2
  • DC=Demand Cost
  • DM=Demand Margin
  • DO=Days out of Stock
  • DQ=Demand Qty Sold
  • DS=Demand Sales
  • ED-End Sale Date
  • EI=End Item Code
  • EO=Economic Order Qty
  • F = Formula
  • FR=First Received on Dt
  • FS=First Sold on Date
  • I = Item Number
  • IA=Item User Amount
  • IB=in BOMP as Component (# of Times)
  • IC=Inventory Class
  • ID=Item User Date
  • IK=in Kit as Component (# of Times)
  • L = Location
  • LC=Last Cost
  • LD=Linkage File Document Date
  • LL=Qty Sugg by LT Reord(LT)
  • LM=Qty Sugg by LT RO+Min/Mult
  • LO=Last Out of Stock Date
  • LP=Last Sales Price
  • LR=Last Received on Date
  • LLast Sold on Date
  • LT=Lead Time
  • M = Margin (Period Aggregate)
  • MC=Material Cost Type
  • MM=Primary Mfg Flag
  • MP=Minimum Price
  • MS=Master Schedule Item
  • M%=Marging %
  • N1=Item Note1
  • N2=Item Note 2
  • N3=Item Note 3
  • N4=Item Note 4
  • N5=Item Note5
  • NB=in BOMP as Neg. Component (# of Times)
  • NK= in Kit as Neg. Component (# of Times)
  • OL=Order Multiple
  • OM=Order Minimum
  • OU=Order UP to
  • P = Price (Item Unit)
  • PC=Product Category
  • PM=Purchase/MFg/Kit/Feature
  • PR=Purchase to Stock Ratio
  • Purchase UOM
  • QA=Qty Allocated
  • QB=Qty BO
  • QH=Qty On Hand
  • QI=Qty Sold in Invoice History (may increase running time if used)
  • QL=Qty Sugg by ReOrd Lvl
  • QM=Qty Sugg by ReOrd+Min/Mult
  • QO=Qty on Order
  • QR=Qty Received
  • QS=Qty Sold (may increase running time if filter by customer)
  • RL=Reorder Level
  • RM=Recommend Minimum Order
  • RR=Routing Revision
  • S = Sales
  • SC=Standard Cost
  • SD=Start Sales Date
  • SE=Serialized Item Flag
  • SF=Stocked Flag
  • SO=Sls Desk # Times O/S
  • SQ=Sales Desk Qty Quoted
  • SR=Selling to Stocking Ratio
  • SS=Safety STock
  • ST=Sales Desk # of Times Qtd
  • SU=Selling Unit of Measure
  • SW=Stock Watch (No of)
  • U = Usage
  • U1=Usage Prior 1 Month
  • U2=Usage Prior 2 Months
  • U3=Usage Prior 3 Monhts
  • U6=Usage Prior 6 Months
  • UA=Average Usage
  • UC=User Defined code
  • UD=User Defined Code Desc
  • UE=User Defined ERG#
  • UH=User Defined Code Hazard
  • UI=User Defined Pickup
  • UN=User Defined Code NMFC
  • US=User Defined Code Class
  • UL=Usage by Item Lead Time
  • UM=Unit of Measure (Stock)
  • UY=Usage Prior 12 Months
  • VN=Vendor Number (Default)
  • V = Volume
  • W = Weight
  • WN=Wish List (Number of)
  • WQ=Wish List Qty
In the Code Type search window, when you highlight each code type, the bottom of that window may display up to three more lines of explanation of the highlighted code. See sample screen below:


Reference B - Current vs. History Data

In the previous section, we discussed the relationship of non-netable locations and how they may affect current and history data presentation.  In short, quantity on hand, allocated, on order...etc. are considered "current" data.  Qty sold, sales amount...etc. are considered "history" data. The following is a list that shows which code types are considered as "Current" or "History" data:

Current Data Code Types (Fields)
  • AB=ATP Qty Balance on 999 Days      
  • AL=ATP Qty Bal at lead Time        
  • AN=ATP Qty Net Change              
  • AP=ATP Qty Bal at LT+ PlanPrd      
  • AQ=Available Qty (OH-Alloc)        
  • AS=ATP PO Trx BF Ship LT          
  • AT=ATP# Trx BF Today              
  • BA=BOMP Avail                      
  • BQ=Balance Qty (OH-Alloc+OO)
  • LL=Qty Sugg by LT reord(LT)        
  • LM=Qty Sugg by LT RO+Min/Mult              
  • LO=Last Out of Stock Date    
  • LP=Last Sale Price          
  • QA=Qty Allocated                
  • QB=Qty BO                      
  • QH=Qty On Hand                  
  • QL=Qty Sugg by ReOrd Lvl          
  • QM=Qty Sugg by ReOrd+Min/Mult      
  • QO=Qty On Order    
History Data  Code Types (Fields)
  • DC=Demand Cost            
  • DM=Demand Margin          
  • DO=Days out of Stock      
  • DQ=Demand Qty Sold        
  • DS=Demand Sales            
  • M=Margin                  
  • M%=Margin%                
  • QR=Qty Received        
  • QI=Qty Sold in Invoice History    
  • QS=Qty Sold                
  • S=Sales                    
  • SO=Sls Desk # Times O/S    
  • SQ=Sales desk Qty Quoted  
  • ST=Sales Desk # of Tiems Qtd
  • SW=Stock Watch (No of)    
  • U=Usage                    
  • U1=Usage MTD              
  • U2=Usage 2 Months          
  • U3=Usage 3 Months          
  • U6=Usage 6 Months          
  • UA=Average Usage          
  • UL=Usage by Item Lead Time
  • UY=Usage 12 Months        
  • WN=Wish List (Number of)  
  • WQ=Wish List Qty          


EMK

Feedback and Knowledge Base