Follow

DemandCaster Data Tables

This document provides a detailed description of all the required and optional tables that may be uploaded to DemandCaster. The platform and options that each table is applicable to is also defined.

Here is a list of all the tables for quick access:

  1. Item Master Table (item.txt)
  2. Customers (customers.txt)
  3. Customer Orders (customerorders.txt)
  4. Product Classes (productclasses.txt)
  5. Vendors (vendors.txt)
  6. Vendor Orders (vendororders.txt)
  7. Locations (locations.txt)
  8. Item Location (itemlocations.txt)
  9. BOM (bom.txt)
  10. BOM - Multi-Location (DRP) (bommultilocation.txt)
  11. BOM Infinite Loop Issue
  12. Shop Orders (shoporders.txt)
  13. Work Center (workcenter.txt)
  14. Routing (routing.txt)
  15. Shop Order Progress (shoporderprogress.txt)
  16. BOM Component Supersession (transitionlinking.txt)
  17. Lot Control (lotcontrol.txt)
  18. Item Tagging (itemtags.txt)
  19. Multi-location Item Tagging (itemtagsmultilocation.txt)
  20. Supersession (productchaining.txt)
  21. Multi-location Item Tagging (itemtagsmultilocation.txt)
  22. Item Life Cycle (itemlifecycle.txt)
  23. User Defined Forecast - Months (userdefinedforecastmonths.txt)
  24. User Defined Forecast - Weeks (userdefinedforecast.txt)
  25. S&OP User Defined Forecast Upload (userdefinedsopforecast.txt)
  26. S&OP Budget Upload (sopbudget.txt)
  27. S&OP Revised Budget Upload (soprevisedbudget.txt)
  28. SOP Do not Forecast (sopdonotforecast.txt)
  29. Alternate Supply Sources (alternatesources.txt)
  30. Batching (containerizationcriteria.txt)
  31. Point of Sale History (posorders.txt)
  32. Days Cover (dayscover.txt)
  33. Estimated Retail Inventory Table (retailinventory.txt)
  34. Forecast by Analogy (sopanalogyedit.txt)
  35. Planning Hierarchy (planninghierarchy.txt)
  36. Customer Specific Pricing (customerspecificprice.txt)
  37. Multi-Currency Rates (currencyrates.txt)
  38. Currency by Customer (customercurrency.txt)
  39. Customer Grouping (customergrouping.txt)
  40. Planning User (planninguser.txt)

1. Item Master Table (item.txt)

This is a required table for both the Advanced Retail and Distribution Forecasting and Planning Platform and Advanced Manufacturing Forecasting and Planning Platform.

The table columns are as follows. All columns are required to be populated unless otherwise noted. If no such value exists in your ERP please default the value to 0.

Each upload updates the data with a time stamp. If the item code no longer exists in the upload, the item remains in DemandCaster with the previously uploaded time stamp to retain history. This allows users to upload on changes with each upload instead of the entire item.txt file. To identify old items, please view the item table in the data maintenance interface via the drop down menu.

  • A - Item/Product Code (max 40 chars): This is the item number. Required column. ERP will be source of master data.
  • B - Item Desc (max 200 chars): The items description. Not a required field but helpful. Required column..Default to 0 if not available. ERP will be source of master data.
  • C - On Hand Quantity (float): The current quantity on hand. Required column. Default to 0 if not available. If the DRP module is enabled, default to 0 for each item since the itemlocation table value takes precedence. ERP is the source of master data.
  • D - Reorder Level Quantity (float): Current reorder point. This is not required field and is used as a point of reference since DemandCaster calculates the items reorder point. Default to 0 if not provided.
  • E - Lead Time (calendar days, int): Current item lead time to purchase or manufacture an item (time of order to receipt). Required column. If not available default to 1. If the DRP module is enabled, default to 0 for each item if the itemlocation table value takes precedence. Either your ERP or DemandCaster can be source of master data.
  • F - Min Order Quantity (float): This is the item minimum order quantity. Default to 0 if not provided. If the DRP module is enabled, default to 0 for each item since the itemlocation table value takes precedence. DemandCaster will calculate this value. DemandCaster will be source of master data.
  • G - Vendor Code (max 20 chars): This is the items primary supplier. This field is left blank if the item is manufactured or received from another location (in DRP). If there is more than one supplier for the given item, please only provide the default supplier. If not an existing vendor then the item is assigned a default "unknown" vendor. Required column. Default to blank if not available. ERP will be source of master data.
  • H - Unit Of Measure (max 5 chars): This is the items stored unit of measure. Required column. Default to 0 if not available. ERP will be source of master data.
  • I - Class Code (max 20 chars): This is the items category code. A category is a logical way of categorizing an item for primarily supply planning purposes though it can also be used in demand planning. If not an existing product class then the item is assigned a default "unknown" product class. Required column. Default to 0 if not available. ERP will be source of master data.
  • J - Cost (each) (float): This is the items current or average purchase or manufactured cost. Required column. Default to 0 if not available. Recommend standard cost. If the DRP module is enabled, default to 0 for each item if the itemlocation table value takes precedence. ERP will be source of master data. However, this value can also be managed in DemandCaster as an option.
  • K - Inventory or Non Inv. (0 (not stock) or 1 (stock)): This defines if an item is stocked in inventory or purchased or produced to order. Required field but you can make all 1 or 0 if not available. If the DRP module is enabled, default to 0 for each item if the itemlocation table value takes precedence. Typically, ERP is the source of the master data however many companies choose to manage in DemandCaster.
  • L - Make, Buy, or Phantom (1 char; M, B, P): This defines if an item is produced internally, purchased externally, or is a phantom in a bill of material. In instances where an item may be both purchased and produced, please specify the default replenishment process of the item. Required column. Default to B if not available. Typically, ERP is the source of the master data however some companies choose to manage in DemandCaster.
  • M - Case pack quantity (float): This is the quantity per case. For example, eggs typically are purchase or sold in cases of 12. It may also be used as a batch size for manufactured items. Required column. Default to 1 if not available. If the DRP module is enabled, default to 0 for each item if the itemlocation table value takes precedence. Typically, ERP is the source of the master data however some companies choose to manage in DemandCaster.
  • N - Case cube (float): This is the volume of the case for storage and shipping purposes. Required column. Default to 0 if not available. Typically, ERP is the source of the master data however some companies choose to manage in DemandCaster.
  • O - Safety Stock (float): This is the items safety stock or minimum stocking level in a min/ max system. Required column. Default to 0 if not available. ERP or DemandCaster can be the source of master data. Most manage in DemandCaster.
  • P - Unit Price (each) (float): This is the items standard price. Required column. Default to 0 if not available. ERP is the source of master data.
  • Q - Storage Code: NO LONGER USED. DEFAULT TO 0.
  • R - Ship By Days (calendar days, int): This field is for the Lot Expiration planning feature. It is the number of days past the production or purchase order completion date that the lot needs to be shipped to the customer by. Default to 0 if not available. Enabled via a system setting. ERP is the source of master data.
  • S - Expiration Days (calendar days, int): This field is for the Lot Expiration planning feature. It is the number of days past the production or purchase order completion date that the lot expire. Default to 0 if not available. Enabled via a system setting. ERP is the source of master data.
  • T - Order Lead Time days (calendar days, int): The number of days from order to expected receipt. Used primarily in production to set an order time that is different than the production lead time to allow the components to explode at the shorter production lead time. This option must be enabled in company settings prior to loading the data. If the DRP module is enabled, default to 0 for each item if the itemlocation table value takes precedence. Enabled via a system setting. ERP or DemandCaster can be the source of master data. Most manage in DemandCaster.
  • U - Yield: The percent factor that is added on top of a suggested planned order in order to accomodate yield loss in production. Default to 0 if there is no yield applied. Not a required field. Enabled via a system setting. ERP is the source of master data.
  • V - Unit of Measure Normalization: For S&OP, the factor that converts the selling unit of measure to a normalized unit of measure at an aggregate level in demand and supply planning hierarchy. Not a required field. Enabled via a system setting. ERP is the source of master data.

2. Customers (customers.txt)

This is a required table for both the Advanced Retail and Distribution Forecasting and Planning Platform and Advanced Manufacturing Forecasting and Planning Platform.

The table columns are as follows. If implementing Demand Planning via S&OP and if you do not care about viewing Demand history in pareto order or by customer detail, you can convert the customers to a generic code and name which will make this table a single row. The customer code here must correspond to the customerorder table since they are relational.

You may also make the customer code anything that defines demand for your specific company (particularly if S&OP is enabled). For example, instead of discrete customer you may choose to use markets, channels, territories, etcetera. Whatever you decided, it must be related to the customer order table customer field.

Each upload updates the data with a time stamp. If the customer code no longer exists in the upload, the code remains in DemandCaster with the previously uploaded time stamp to retain history. This allows user to update the table with only changes with each subsequent upload.

  • A - Customer Code (max 20 chars): This is the customer code. Pulled from the customer table. Required. Can be a generic code. Your ERP is the source of master data.
  • B - Customer Name (max 100 chars): This is the name of the customer. Pulled from the customer table. Required. Can be a generic name. Your ERP is the source of master data.

3. Customer Orders (customerorders.txt)

This is a required table for both the Advanced Retail and Distribution Forecasting and Planning Platform and Advanced Manufacturing Forecasting and Planning Platform.

For multi-location companies with the DRP option, please ensure all inter company transfer orders are excluded from the customer order file. Customer orders should only be external sales orders. If necessary, internal transfer orders should be treated as purchase orders or production orders and included in the vendororders.txt or shoporders.txt file.

Provide a minimum of 1 year of sales history - 3 years is preferred.

Many clients ask how to handle returns. If S&OP is enabled we recommend including returns and credits in the historical data in order to ensure the historical revenue matches the companies profit and loss. A separate row for returns and credits will be included in the demand plan grid. This data can be excluded via an option in system settings. Because we are using history to size inventory, we need to use the full history since demand was created for the item regardless of the subsequent disbursement.

Another question that is often asked is how to handle configure to order (CTO) demand. The challenge with CTO is that they are often one off sales and because of this exporting all the sales records and BOM's related to these CTO products is not practical. As such, we recommend either tying CTO sales history to a generic planning BOM to drive demand for components or converting the CTO sales to the component that the CTO product drove demand for as independent sales for the component.

The default upload profile is to delete the prior customer orders and replace with new data. Though we often are asked if only changes can be uploaded with each refresh, this is possible however there is risk. For example, if a user deletes a record in the ERP, the data upload process will not be able to recognize there was a deletion. As such, the safest option is to upload a set period of history each time on a rolling monthly basis. If you have minimal to no past due customer orders, you may upload 1 to 2 full months of history in addition to month to date. For example, for the month of May, you would upload March, April, and month to date May. In June, it could then index forward one month to April, May, and month to date June. This allows for smaller incremental uploads while allowing the data load to proceed faster without the need to find and replace specific lines that may have changed.

This table is related to items, customers, and locations via the corresponding item, customer, and location codes.

The table columns are as follows - All data comes from your ERP

  • A - Order Number - Line (max 20 chars): Order number is typically from the sales order header table and the line is from the sales order detail table. It is preferred that a dash be added between the order and line but it is not required. This is a required field.
  • B - Status (1 char for Open, Closed, Planned): Characters are O = open, C = closed, and P = planned. If a line has been cancelled, please use the status C but leave the ship quantity and date blank. This is a required field. To illustrate forecasting, we only need closed orders. DemandCaster logic is if Open order has the same Due Qty and Ship Qty then DemandCaster considers the order Closed regardless of the satus. If there is a balance due, the quantity is considered open unless the status is C.
  • C - Order Date (date): The date the order was placed by the customer. This is typically pulled from the order header and is a required field.
  • D - Customer Code (max 30 chars): This is the customer who placed the order. This is typically pulled from the order header. Required field. You can apply a generic customer code if you do not want to view demand history by discrete customer demand or another definition of customer as described in the customer table.
  • E - Item/Product Code (max 40 chars): This is the item that was ordered. This is typically pulled from the order detail table. Required field.
  • F - Order Quantity (float): This is the original quantity ordered by the customer. If the order quantity is not available populate column with the ship quantity as a default order quantity for the item for a previously shipped item. This is typically pulled from the order detail table. Required field.
  • G - Due Date (date): This is the original due date for the item. If this date is not available populate column with the ship date as a default due date for the item for a previously shipped item. If appropriate, the due date may be generically applied in situations where there is a delivery policy in place i.e. we will ship 24 hours after the order date. In addition, in situations where you would like to measure on time performance and also forecast based on the customer original due date request date, we recommend adding a rule to the SQL that uses the original due date and/or due quantity when the order is closed and the negotiated due date and due quantity when the order is opened. This is typically pulled from the order detail table. Required field.
  • H - Date Shipped (date): This is the actual ship date if the item was shipped. Leave blank if not shipped. If partially shipped, include the date of the shipment but leave the order line status as O for open. If partially shipped and the order line has been closed change the status to C for closed. This is typically pulled from the order detail table. Required field.
  • I - Quantity Shipped (float): This is the actual ship quantity. Leave blank if not shipped. If partially shipped, include the quantity of the shipment but leave the order line status as O for open. If partially shipped and the order line has been closed enter the ship quantity and change the status to C for closed. This is typically pulled from the order detail table. Required field. Default to 0 if not available.
  • J - Sales Price (each): The unit sales price of the item. If the value is a lump sum divide the total sales dollars by the units to get the unit price. For S&OP since the software is projecting revenue the unit sales price should correlate to the invoice value. This is typically pulled from the order detail table. If your company is going to plan with multiple currencies, the sales price and cost of the order line should be based on the local currency. Required field. Do not include the currency symbol. Default to 0 if not available.
  • K - Cost (each): The unit cost of the item at the time of shipment if available. If the value is a lump sum divide the total cost dollars by the units to get the unit cost. This is typically pulled from the order detail table. We recommend providing the historical cost when running S&OP since this value will be used when calculating historical gross margin. Do not include the currency symbol. Default to 0 if not available.
  • L - Location (max 20 chars; required for DRP): This is the location the item was shipped from. Required if DRP module is enabled. In certain instances the shipment was made from a non-primary location if there was a stockout at the primary location. In these cases we recommend normalizing the location to the primary ship from location for the item.

4. Product Classes (productclasses.txt)

This is a required table for both the Advanced Retail and Distribution Forecasting and Planning Platform and Advanced Manufacturing Forecasting and Planning Platform.

The product class code must correspond to the item table since they are relational.

Each upload updates the data with a time stamp. If the product class code no longer exists in the upload, the product class remains in DemandCaster with the previously uploaded time stamp to retain history. This allows user to update the table with only changes with each subsequent upload.

The table columns are as follows - all data comes from ERP:

  • A - Class Code (max 20 chars): This is the items category code. A category is a logical way of categorizing an item for planning purposes.
  • B - Class Name (max 100 chars): The name of the class.

5. Vendors (vendors.txt)

This is a required table for both the Advanced Retail and Distribution Forecasting and Planning Platform and Advanced Manufacturing Forecasting and Planning Platform.

The vendor code must correspond to the item and vendor orders table since they are relational.

Each upload updates the data with a time stamp. If the vendor code no longer exists in the upload, the vendor code remains in

DemandCaster with the previously uploaded time stamp to retain history. This allows user to update the table with only changes with each subsequent upload.

The table columns are as follows - all data comes from ERP:

  • A - Vendor Code (max 20 chars): This is the vendor code. Pulled from the vendor table.
  • B - Vendor Name (max 100 chars): This is the name of the vendor. Pulled from the vendor table.

6. Vendor Orders (vendororders.txt)

This is a required table for both the Advanced Retail and Distribution Forecasting and Planning Platform and Advanced Manufacturing Forecasting and Planning Platform.

The vendor order table may also be called purchase order or supplier order. Require a minimum of 6 months of history (1 year preferred) if interested in viewing on time and lead time performance for suppliers. For multi-location (DRP) companies, please consider all open and closed inter- company orders as purchase orders and include in the vendororders.txt file.

Each upload deletes the prior vendor orders and replaces with new data. Users may choose to only delete and replace a limited time frame i.e. last 3 to 6 months in order to minimize the amount of data being updated. We recommend 6 months of history to upload.

The table columns are as follows - all data comes from ERP:

  • A - Order Number and Line (max 20 chars): Order number is typically from the vendor order header table and the line is from the vendor order detail table. It is preferred that a dash be added between the order and line but it is not required. Required field.
  • B - Status (1 char for Open, Closed, Planned): Characters are O = open, C = closed, and P = planned or In-Transit / LCT. In transit / LCT orders will show up as green in the replenishment orders column in the items requirement plan. If a line has been canceled, please use the status C but leave the ship quantity and date blank. May load only open orders to view impact on requirement planning. Required field.
  • C - Order Date (date): The date the order was created. This is typically pulled from the vendor order header and is a required field. For blanket vendor orders if the release to vendor date is available, please provide this date in place of the order date. Required field.
  • D - Vendor Code (max 20 chars): This is the vendor where the item is/was purchased. This is typically pulled from the vendor order header. Can be a generic value. Required field.
  • E - Item/Product Code (max 40 chars): This is the item that was ordered. This is typically pulled from the vendor order detail table. Required field.
  • F - Order Quantity (float): This is the original quantity ordered from the vendor. If the value is not available default to delivered (receipt quantity). This is typically pulled from the vendor order detail table. Required field.
  • G - Scheduled Delivery Date (date): This is the original due date for the item. If this date is not maintained use the ship date as a default due date for the item for a previously shipped item. If appropriate, the completion date may be generically applied using the item lead time. This is typically pulled from the vendor order detail table. Required field.
  • H - Actual Delivered Quantity (float): This is the actual ship quantity. Leave blank if not shipped. If partially shipped, include the quantity of the shipment but leave the order line status as O for open. If partially shipped and the vendor order line has been closed enter the ship quantity and change the status to C for closed. This is typically pulled from the vendor order detail table. Required field.
  • I - Actual Delivery Date (date): This is the actual receipt date if the item was shipped. Leave blank if not shipped. If partially shipped, include the date of the shipment was received but leave the order line status as O for open. If partially shipped and the order line has been closed change the status to C for closed. This is typically pulled from the vendor order detail table. Required field.
  • J - Location Code (max 20 chars;  required for DRP): This is the location where the vendor/ purchase order was received. Required if DRP module is enabled. This is typically pulled from the vendor order header table and is a required field for multi-location companies.

7. Locations (locations.txt)

This is a required table for the optional DRP Add-On

This table is required only if you have product stored or produced in multiple warehouse or manufacturing locations (DRP). These are physical warehouse and/or manufacturing locations and not inventory locations within a site. Each upload updates the data with the latest data. Old locations are retained to view historical plans.

The table columns are as follows - all data comes from ERP:

  • A - Location Code (max 20 chars): Required
  • B - Location Name (max 50 chars): Required

8. Item Location (itemlocations.txt)

This is a required table for the optional DRP Add-On

This table is required only if product is stored or produced in multiple warehouse or manufacturing locations (DRP module option). It defines the item attributes at a specific site (destination) and where the inventory is primarily received from (source). The itemlocation table is similar to a bill of material table in that it establishes top town parent child relationships.

With each upload the table updates existing item values, deletes missing values, and adds new. As a result it is critical to upload the entire itemlocation table each time and to provide data for locations that are real inventory locations even if the item is made or purchased to order or currently has 0 on hand. If it not provided DemandCaster will not consider these as an inventory location.

The table columns are as follows:

  • A - Item/Product Code (max 40 chars): This is the item number being managed at a specific location. Required field. ERP will be source of master data.
  • B - On Hand Quantity (float): This is the current on hand quantity at the given location. Items that are at 0 inventory but are normally manufactured or inventoried in the location should have a value of 0 in this field. Required field. ERP will be source of master data.
  • C - Location Code (max 20 chars): This is the location where the item is located. It is the destination location from a source location or from a supplier. Required field. ERP will be source of master data.
  • D - Order Lead Time days (calendar days, int): This is the time to place an order at a location. Optional, populate with 0 if not provided. If not provided make sure the order lead time check box is unchecked in company settings. Typically this value is managed in DemandCaster.
  • E - Transfer Lead Time days (calendar days, int): Time to transfer from location to location. Optional, populate with 0 if not provided. If not provided make sure the transfer lead lead time check box is unchecked in company settings. Typically this value is managed in DemandCaster.
  • F - Primary Source Location Code (max 20 chars): If the item is replenished from another location, must specify the primary source location the product is received from in this field. If the item at the specified location is manufactured at the same location please add the same location code. If procured from an outside supplier, leave the primary source location code blank. The item table specifies the supplier location. This information is important so that DemandCaster knows where to place suggested replenishment orders. Required field. Typically this value is managed in DemandCaster.
  • G - Inventory or Non Inv. (bit; 0 (not stock) or 1 (stock)): This defines if an item is stocked or not stocked at the given location.
  • H - Lead Time days (calendar days, int): This is the time to receive a replenishment order from another location, manufacturer the item in the current location, or receive the item from a supplier. This is not a required field but is strongly recommended. The values may be manually entered within DemandCaster if not available via the upload. Leave blank if the item table value is used. ERP or DemandCaster can be source of master data.
  • I - Case Pack Quantity: A location may have a case pack or batch size that is location specific. Leave blank if the item table value is used. ERP or DemandCaster can be source of master data.
  • J - Current Cost (each): A location may have a cost that is location specific. Leave blank if the item table value is used. Typically ERP is the source of master data.
  • K - Min Order Quantity (float): This is the item location specific minimum order quantity. Leave blank if the item table value is used. ERP or DemandCaster can be source of master data.
  • L - Safety Stock: This is the item location specific safety stock. Leave blank if no location specific safety stock is used. Most often DemandCaster is the source of master data.

9. BOM (bom.txt)

Required for manufacturing companies in order to explodes sales forecasts and requirements through BOM.

For multi-level BOM's the subassembly levels are "children" of higher level finished goods and parents to lower level assemblies or components. DemandCaster will automatically construct the hierarchy based on this parent - child structure. This table is required only if component level planning is required.

Each upload updates the data with a time stamp. The time stamp is used to view historical plan based on the BOM at that time.

Current plans use the latest time stamped BOM. Upload the full BOM file with each upload. An old time stamp will not be used in a current requirement plan.

The table columns are as follows - ERP is the source of master. BOM's can not be created or modified within DemandCaster at this time.

  • A - Parent Item/Product Code (max 40 characters): Required field.
  • B - Child Item/Product Code (max 40 chars): Required field.
  • C - Qty in Parent (float): Required field.

It is recommended for companies that have numerous one time make to order finished goods, that the customer orders table explode the finished goods requirements to the component level to avoid the need to have many one time BOM's imported into DemandCaster.

10. BOM - Multi-Location (DRP) (bommultilocation.txt)

Required for multi-location manufacturing companies in order to explodes sales forecasts and requirements through BOM at a specific location.

This allows a finished good item to have a unique BOM's based on the production location. This replaces the single location BOM table.

For multi-level BOM's the subassembly levels are "children" of higher level finished goods and parents to lower level assemblies or components. DemandCaster will automatically construct the hierarchy based on this parent - child structure. This table is required only if component level planning is required.

Each upload updates the data with a time stamp. The time stamp is used to view historical plan based on the BOM at that time.

Current plans use the latest time stamped BOM. Upload the full BOM file with each upload. An old time stamp will not be used in a current requirement plan.

The table columns are as follows - ERP is the source of master. BOM's can not be created or modified within DemandCaster at this time.

  • A - Parent Item/Product Code (max 40 characters): Required field.
  • B - Child Item/Product Code (max 40 chars): Required field.
  • C - Qty in Parent (float): Required field.
  • D - Location Code (max 20 characters): Required field. This is the location where the item is manufactured, kitted, or assembled.

It is recommended for companies that have numerous one time make to order finished goods, that the customer orders table explode the finished goods requirements to the component level to avoid the need to have many one time BOM's imported into DemandCaster.

11. BOM Infinite Loop Issue

Please make sure there are no instances where a parent has a child that could also be a parent. This causes an infinite loop. If such a loop is created, the BOM does not load. You can check for such an error by viewing the data upload error log. A loop will be noted as "Cycle - Item -> Item; Item -> Item;"

12. Shop Orders (shoporders.txt)

Required for manufacturing companies

The shop order table may be called different names including work orders, production orders, or jobs. Applicable to companies that produce, assemble, or kit finished goods. Require a minimum of 6 months of history (1 year preferred) if interested in viewing shop order on time and lead time performance.

Each upload deletes the prior shop orders and replaces with new data. Users may choose to only delete and replace a limited time frame i.e. last 3 to 6 months in order to minimize the amount of data being updated. We recommend 6 months of history to be uploaded.

The table columns are as follows - all data comes from ERP:

  • A - Shop Order Number and Line (max 20 chars): Order number is typically from the shop order header table and the line is from the shop order detail table. It is preferred that a dash be added between the order and line but it is not required. Required field.
  • B - Status (1 char for Open, Closed, Planned): Characters are O = open, C = closed, or P = planned. If a line has been cancelled, please use the status C but leave the completion quantity and date blank. Planned means the order has not been released to production thus no inventory has been issued against the shop order. Required field.
  • C - Order Date (date): The date the order was created. This is typically pulled from the shop order header and is a required field. For a shop order with multiple lines if the release to production date is available, please provide this date in place of the order date. Required field.
  • D - Item/Product Code (max 40 chars): This is the item that was ordered. This is typically pulled from the shop order detail table. Required field.
  • E - Order Quantity (float): This is the original quantity ordered. This is typically pulled from the shop order detail table. Required field.
  • F - Scheduled Completion Date (date): This is the date the order was/is scheduled to be completed. If this date is not maintained we will used the actual completion date as a default completion date for the item for a previously shipped item. If appropriate, the completion date may be generically applied using the item lead time. This is typically pulled from the order detail table. Required field.
  • G - Actual Completed Quantity (float): This is the actual completion quantity. Leave blank if not completed. If partially completed, include the quantity of the completion but leave the order line status as O for open. If partially completed and the order line has been closed enter the completion quantity and change the status to C for closed. This is typically pulled from the shop order detail table. Required field.
  • H - Actual Completion Date (date): This is the actual completion date if the item was completed. Leave blank if not completed. If partially completed, include the date of the completion but leave the order line status as O for open. If partially completed and the order line has been closed change the status to C for closed. This is typically pulled from the shop order detail table. Required field.
  • I - Location Code (max 20 chars;  required for DRP): This is the location where the order was produced. This is typically pulled from the shop order header table and is a required field for multi-location companies.

Note: If a work order (job / shop order) is created in your system but inventory has not been issued to the order, please apply the P status to the work order. This will allow DemandCaster to allocate inventory against the job if back-flushing is not the inventory management approach applied to the item.

13. Work Center (workcenter.txt)

Required for manufacturing companies with capacity planning

Required if the capacity planning and scheduling module is enabled. This is the list of work centers used in the routing. Each upload updates the data with a time stamp. This allows user to update the table with only changes with each subsequent upload. If the work center number no longer exists in the upload, the work center remains in DemandCaster with the previously uploaded time stamp. To identify old work centers, please view the work center table in the data maintenance interface in the manufacturing menu group via the drop down menu. The table columns are as follows:

The table columns are as follows - all data comes from ERP:

  • A - Work Center Number: Required field 
  • B - Work Center Name: Required field 
  • C - Location Code (max 20 chars;  required for DRP): For multi-location companies. Ties the work center to a specific location if DRP is enabled.

14. Routing (routing.txt)

Required for manufacturing companies with capacity planning

Required for manufacturing companies if capacity planning and scheduling module is enabled. This is the items standard / primary routing. Each upload deletes the prior routings and replaces with new data.

The table columns are as follows - all data comes from ERP:

  • A - Item/Product Code (max 40 chars): This is the item that is produced. Required field. 
  • B - Operation Number (max 10 char): This is the operation number of the routing. Typically a sequential number sorted in increasing order. The smallest operation number is typically the first operation. Required field. 
  • C - Work Center Number: Work center that the operation is assigned to. Required field. 
  • D - Operation Description: Description of the operation. This is not a required field. 
  • E - Setup Time (minutes or hours): The number of hours (decimal) or minutes required to set up the specific operation. Default to 0 if not available. 
  • F - Run Time (minutes or hours): The number of hours (decimal) or minutes to run one part. Take the longest of the labor or machine time. 
  • G - Lead Time: This is the number of days required to process an outside operation. Not required. Default to 0 if not used. 
  • H - Location Code (max 20 chars;  required for DRP): For multi-location companies. If the routing is tied to specific location otherwise leave blank.

15. Shop Order Progress (shoporderprogress.txt)

Optional table for manufacturing companies with capacity planning

Required for manufacturing companies if capacity planning and scheduling module is enabled and if they wish to track shop order progress. The routing currently released to production for the item is provided here unless the general routing is applicable. Each upload deletes the prior routings and replaces with new data.

The table columns are as follows - all data comes from ERP:

  • A - Shop Order Number and Line (max 20 chars): Required field. 
  • B - Operation Number (max 10 char): Required field. 
  • C - Work Center Number: Required field. 
  • D - Completed Quantity (float): 0 if none completed. Required field. 
  • E - Completed Date (date): The date the routing operation was completed. Required field. 
  • F - Status (1 char): Y = In Process, N = Not Started, C = Completed. Required field. 
  • G - Item/Product Code (max 40 chars): Item being produced. Required field. 
  • H - Setup Time (minutes or hours): The number of hours (decimal) or minutes required to set up the specific operation. Required field. 
  • I - Run Time (minutes or hours): The number of hours (decimal) or minutes to run one part. Take the longest of the labor or machine time. Required field. 
  • J - Lead Time: This is the number of days required to process an outside operation. Required field. 
  • K - Location Code (max 20 chars;  required for DRP): For multi-location companies. If the routing is tied to specific location otherwise leave blank. Required if DRP is enabled.

16. BOM Component Supercession (transitionlinking.txt)

Optional table

The table columns are as follows:

  • A - Item/Product Code
  • B - Linked Item/Product Code
  • C - Location code (required only per multiloc. business nodes)
  • D - Type (0 for soft, 1 for hard; 2 for temporary)
  • E - Start Date 
  • F - End Date (required if type is 2)

The temporary component linking made by the upload is applied at all parents. If you wish to apply the linking to a single parent item, please do so in data maintenance.

17. Lot Control (lotcontrol.txt)

Optional table

Applicable for companies that are concerned with inventory expiration i.e. foods and perishable items. The lot control table is used to include ship by and expiration dates in inventory planning. The lot quantity should match the on hand quantity in either the item table or itemlocation table.

With each upload the prior data is deleted and replaced with the latest uploaded data.

The table columns are as follows:

  • A - Item/Product code (max 40 chars): Item being produced. Required field.
  • B - Lot code (max 20 chars): Lot number assigned. Required field.
  • C - Lot qty (float): Quanity in the lot. Required field.
  • D - Ship by date (date): The date the lot needs to ship by prior to expiring. Required field.
  • E - Exp. date (date): The date the lot is no longer available for even secondary sources. Required field.
  • F - Location code (max 20 chars, required for DRP): The location where the lot is stored. Required field.

18. Item Tagging (itemtags.txt)

Optional table

An item code, may have multiple tags. Each row will be one item and one tag. An item may appear more than once with each row having a different tag. This information may be used for simple tagging or to help create hierarchical sales aggregation. This is uploaded on demand or it can be automated via the integration. Not a required table.

With each upload delete the values that are no longer in the upload and add new codes. If the tag is entered via DemandCaster item tag interface, the values remain regardless of what is included in the uploaded value.

The table columns are as follows:

  • A - Item/Product Code (max 40 chars)
  • B - Item Tag (max 250 chars)

19. Multi-location Item Tagging (itemtagsmultilocation.txt)

Optional table

Same as above but applies tags by item and location. This is uploaded on demand or it can be automated via the integration. Not a required table.

With each upload delete the values that are no longer in the upload and add new codes. If the tag is entered via DemandCaster item tag interface, the values remain regardless of what is included in the uploaded value.

The table columns are:

  • A - Item/Product Code (max 40 chars): Required field.
  • B - Location Code (max 20 chars): Required field.
  • C - Tag (max 250 chars): Required field.

20. Supersession (productchaining.txt)

Optional table

This table is used when linking new items to old items en masse.

With each new upload the links are updated based on the value in column A. Any prior uploaded links are not removed.

The table columns are as follows:

  • A - New Item code (max 40 chars)
  • B - Old Item (max 40 chars): Item the new item is replacing
  • C - Linking Type: 0 (full linking) or 1 (forecast only). In case it is empty - assumed 0 (full linking)

21. Supersession - multi-location (productchainingmultilocation.txt)

Optional table

Same as above but location based. This is uploaded on demand only. There is no automation at this time. Not a required table.

With each new upload the links are updated based on the value in column A. Any prior uploaded links are not removed.

  • A - Item Code (max 40 chars)
  • B - Item Location
  • C - Linked Item Code
  • D - Linked Item Location
  • E - Linking Type: 0 (full linking) or 1 (forecast only). In case it is empty - assumed 0 (full linking)

22. Item Life Cycle (itemlifecycle.txt)

Optional table

This table automates the birth date and discontinue date of item and item/locations (if multi-location is enable) for finished goods

  • A - Item (max 40 chars)
  • B - Birth Date (date): Leave blank if item is already active.
  • C - Discontinue Date (date): Leave blank if item has no discontinue date.
  • D - Location: Only if multi-location company otherwise leave blank.

23. User Defined Forecast - Months (userdefinedforecastmonths.txt)

User Defined Forecast - Months (userdefinedforecastmonths.txt) - Optional Table

Optional table

These are loaded to requirement planning forecasts when S&OP is not provisioned. This is uploaded on demand only. There is no automation at this time. Not a required table.

The user may upload user defined forecasts that are either common or location specific. Type 1 designates a common forecast. Type 2 designates a location specific forecast.

When Type = 1 and Location Code is empty – this means the item has a distribution behavior and there are one or more warehouses which will split the forecast between them via a percentage distribution using historical demand.

When Type = 1 and Location Code is not empty – this means the item has a manufacturing behavior and there is one manufacturing location and one or more warehouses which will split the forecast values. There could be a 2nd row with Type = 1 for the same item – this happens when there is a 2nd manufacturing location for the item.

When Type = 2 the Location Code cannot be empty – this means the forecast values are to be applied to a specific location. This is the most common option.

Leave the column headers in the file so DemandCaster will know what date backets to apply the forecast against.

With each new upload the values are updated or loaded as new based on the item code in column A. Any prior uploaded values are not removed.

The table columns are as follows:

  • A - Item Code (max 40 chars)
  • B - Type: 1 – Common, 2 – Location specific
  • C - Loc. Code: The location the forecast is being applied. If blank, the forecast will be distributed by percentage to the location.
  • D - Day 1 Month 1 Forecast
  • E - Day 1 Month 2 Forecast
  • F - Day 1 Month 3 Forecast
  • G - Day 1 Month 4 Forecast
  • H - Day 1 Month 5 Forecast
  • I - Day 1 Month 6 Forecast
  • J - Day 1 Month 7 Forecast
  • K - Day 1 Month 8 Forecast
  • L - Day 1 Month 9 Forecast
  • M - Day 1 Month 10 Forecast
  • N - Day 1 Month 11 Forecast
  • O - Day 1 Month 12 Forecast
  • Etc

24. User Defined Forecast - Weeks (userdefinedforecast.txt)

Optional table

See explanation above. The only differences between the weekly forecast versus the monthly, is the weekly includes customer specified minimum and maximum stocking values and on hand quantities. This is uploaded on demand only. The week start date is a Sunday. There is no automation at this time. Not a required table.

With each new upload the values are updated or loaded as new based on the item code in column A. Any prior uploaded values are not removed.

  • A - Item Code (max 40 chars)
  • B - Type: 1 – Common, 2 – Location specific
  • C - Loc. Code: The location the forecast is being applied. If blank, the forecast will be distributed by percentage to the location.
  • D - LSL: This is the customers lower stocking limit. Leave blank if not applicable.
  • E - USL: This is the customers upper stocking limit. Leave blank if not applicable.
  • F - Past Due: This is the customer on hand quantity at their location. Leave blank if not applicable.
  • G - Week 1 Forecast
  • H - Week 2 Forecast
  • I - Week 3 Forecast
  • J - Week 4 Forecast
  • K - Week 5 Forecast
  • L - Week 6 Forecast
  • M - Week 7 Forecast
  • N - Week 8 Forecast
  • O - Week 9 Forecast
  • P - Week 10 Forecast
  • Q - Week 11 Forecast
  • R - Week 12 Forecast
  • S - Etc.

25. S&OP User Defined Forecast Upload (userdefinedsopforecast.txt)

Optional S&OP table

The details related to preparing this file is covered in the article Uploading a User Defined Forecast / Demand Plan (S&OP Platform)

26. S&OP Budget Upload (sopbudget.txt)

Optional S&OP table

The details related to preparing this file is covered in the article S&OP Budget and Revised Budget Management

27. S&OP Revised Budget Upload (soprevisedbudget.txt)

Optional S&OP table

The details related to preparing this file is covered in the article S&OP Budget and Revised Budget Management

28. SOP Do not Forecast (sopdonotforecast.txt)

Optional S&OP table

This table automatically turns off forecasting in demand planning as opposed to turning off manually in pareto.

With each new upload the values are updated or loaded as new based on the customer code and item code relationship. Any prior uploaded values are not removed.

To reverse a do not forecast, we upload the same file, but add a 0 to column C to designate that the setting should be reversed.

  • A - Customer Code
  • B - Item Code
  • C - Add 0 to reverse a do not forecast.

29. Alternate Supply Sources (alternatesources.txt)

Optional table

Alternate sources is a function that is included as part of the constrained supply logic in multi-location (DRP) planning. The premise is to define an alternate location for a given item that is sourced from a specific location only if the primary location is unable to fulfill the requirement of the item. This is considered only if there is a shortage within the defined constrained supply time frame. In addition, this is only related to locations. Alternate sources from external suppliers are managed in the item data maintenance.

The file is as follows:

  • Item Code (max 40 chars): The item number
  • Location Code: The stocked location of the item
  • Alternate Location Code: The alternate supply (source) location of the item

30. Batching (containerizationcriteria.txt)

Optional table

See the article Batching for more information. The file is as follows:

  • A - Item Code
  • B - Cost
  • C - Length
  • D - Weight
  • E - Cube
  • F - Location Code (mandatory for multi-location business nodes)

31. Point of Sale History (posorders.txt)

Load POS history to the demand planning table. This is an automated table via the data connector. It can also be uploaded manually. This data is used to drive the statistical forecast of the POS demand plan which in turn drives the net demand plan.

With each new upload the prior values are deleted uploaded as new.

  • A - Cust Code
  • B - Item Code
  • C - Date Sold
  • D - Qty Sold
  • E - Selling Price

32. Days Cover (dayscover.txt)

Used in Net Demand Planning

Days Cover - like the current UDF S&OP upload above - item code, customer code, bucket dates and the corresponding days. This table is used to define the number of days on hand of inventory should be maintained at the customer location by week.

This is a similar structure to the user defined forecast upload as documented in the article Uploading a User Defined Forecast / Demand Plan (S&OP Platform)

  • A - Customer Code
  • B - Item Code
  • C, D, E... - Bucket dates per Days Cover

33. Estimated Retail Inventory Table (retailinventory.txt)

Used in Net Demand Planning

This table defines the inventory level of the customer at a specific point in time as determined by the snapshot date.

The current inventory level that the POS demand plan is netted against is estimated as follows using the snap shot date as the starting point for the calculation: Inventory at snapshot date + shipments made to customer - POS sales at the customer

The table is structured as follows:

  • A - Customer Code
  • B - Item Code
  • C - Inventory Level
  • D - Snapshot Date (the date the inventory level was measured)

34. Forecast by Analogy (sopanalogyedit.txt)

Forecast by Analogy (sopanalogyedit.txt) - Optional Table

Optional S&OP table

When forecasting via Demand Planning, this table creates a forecast by applying a distribution percentage of a total project demand over a period of time.

  • A-1: Level that the distribution percentage is applied
  • A-2: The context level above the distribution percentage level starting in row 3
  • B-2: Item/Product code starting in row 3
  • C-3: Total demand to be distributed for the context
  • D-3 and beyond:  Percent of demand applied to the period

If applying the analogy edit to a level that is above the item, the customer code is in column B and the level node is in column A.

35. Planning Hierarchy (planninghierarchy.txt)

Optional S&OP table

Please read the article Creating an S&OP Planning Hierarchy by uploading or via automation to learn more. The file is setup as follows:

  • Column A: Name of top level
  • Column B: Node within the level
  • Column C: Name of level below
  • Column D: Node within level

In each row, column C and D will be associated with column A and B. The relationship between parent (column B) and child (column D) is one to many therefore, you cannot have the same name child node going to more than one parent node.

Please note the following:

  • Each upload handles the relationship between two levels. You may stack multiple levels in the upload. See upload sample below. Starting in row 8278 and above, the hierarchies are the product group and design level. Starting in row 8279 and below, the hierarchy is between the design and item levels.
  • You may combine the customer hierarchy and the item hierarchy in the same file.
  • This file will not only establish the relationships between the nodes and level, but every node which does not currently exist will also be automatically created.
  • Once the file is prepared, name the file planninghierarchy.txt and save as a tab delimited text file. Zip the file and follow the Data Upload process. Repeat the process until all the level nodes and relationships have been created. This process may be automated as well.
Planning Hierarchy (planninghierarchy.txt) - Optional Table

36. Customer Specific Pricing (customerspecificprice.txt)

Optional S&OP table

In certain cases, a customer may have specific pricing for a given item. In this case a table may be uploaded that specifies the pricing by by item and by customer.

The table has the following columns:

  • Item Code
  • Customer Code
  • Sales Price

Please note to use this setting, the Pricing setting in System settings should be set to Customer Specific Pricing

37. Multi-Currency Rates (currencyrates.txt)

DemandCaster supports multi-currency. This table sets the rates by currency that is in use at the company.

  • Column A: Currency Name
  • Column B: Rate against the global currency

38. Currency by Customer (customercurrency.txt)

DemandCaster supports multi-currency. This table sets the rates currency basis by customer. Please note that if this option is used, the customer order table must represent each sales order line in its local currency.

  • Column A: Customer Code
  • Column A: Currency Name  should exists in the system ( uploaded via the currencyrates.txt file )

39. Customer Grouping (customergrouping.txt)

If there is a need to group customers together to form a new customer based forecast group, you can do so as an upload file.

  • Column A: Customer code
  • Column B: Customer group name

For groups already created from the user interface per the article Customer Groups Creation, if there is at least one occurrence in the file, it deletes all previously linked members and the inserts those from the file. If there's no occurrence in the file - nothing is changed.

40. Planning User (planninguser.txt)

The planner action includes a field for planners. To manually upload planners, please prepare the file planninguser.txt with the following columns:

  • Column A: User Email (must exists in DemandCaster) 
  • Column B: Product code
  • Column C: Location code
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk