DemandCaster Data Tables

All 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
  2. Item Location
  3. Locations
  4. Customers
  5. Customer / Sales Orders
  6. Product Classes
  7. Vendors
  8. Vendor / Purchase Orders
  9. Transfer Order
  10. BOM
  11. Shop / Work Orders
  12. Work Center
  13. Routing
  14. Shop Order Progress
  15. Lot Control
  16. Item Tagging
  17. Linking
  18. Transition Linking
  19. Item Life Cycle
  20. User Defined Forecast - Months - only applicable to advanced requirements planning
  21. User Defined Forecast - Weeks  - only applicable to advanced requirements planning
  22. User Defined SOP Forecast - only applicable to advanced business planning
  23. Alternate Supply Sources
  24. Containerization
  25. Batching Template
  26. Co-Product Template
  27. Customer Grouping
  28. Planning User
  29. Base Entities Associations
  30. Manual Issued Quantity

1. Item Master Table (item.txt)

This is a required table.

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. This value may be overwritten within 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 - Expire 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 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 - 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. Item Location (itemlocations.txt)

This is a required table.

This table 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.

The item location and item tables work in concert with one another. If you delete a record in the item data maintenance, it will delete it in the itemlocation data maintenance view. 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 above image shows the required columns and optional columns. Columns in green are related to Company settings initiated in the back end as shown. If these settings are not enabled, the column is not required. Columns in red are optional columns.

The table columns are as follows:

  • Item/Product Code (max 40 chars): Required field. This is the item number being managed at a specific location. ERP will be source of master data.
  • On Hand Quantity (float): Required field. 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. ERP will be source of master data.
  • Location Code (max 20 chars): Required field. This is the location where the item is located. It is the destination location from a source location or from a supplier. ERP will be source of master data.
  • Order Lead Time days (calendar days, int): Optional field required to be enabled in company settings. This is the time to place an order at a location. Populate with 0 if value is not available for a SKU. Typically this value is managed in DemandCaster.
  • Transfer Lead Time days (calendar days, int): Optional field required to be enabled in company settings. 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.
  • Ship By Days (calendar days, int): Optional field required to be enabled in company settings. Populate only if the system setting "Lot Control Location Specific" is enabled under data upload in company settings. 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. ERP is the source of master data. This is enabled via company settings. Disregard if not enabled. Default to 0 if feature is enabled and data is not available via ERP.
  • Expire By Days (calendar days, int): Optional field required to be enabled in company settings. Populate only if the system setting "Lot Control Location Specific" is enabled under data upload in company settings. 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. This is enabled via company settings. Disregard if not enabled. Default to 0 if feature is enabled and data is not available via ERP.
  • Vendor: Optional field required to be enabled in company settings. Populate if the system setting "Use Location Specific Vendor in Upload" is enabled under data upload in company settings. Sets the vendor by location. If not enabled or blank, the system will populate the vendor in the item table.
  • Make / Buy: Optional field required to be enabled in company settings. "Use Make/Buy in ItemLocation Upload" is enabled under data upload in company settings. Sets the make / buy replenishment based on location. If not enabled or blank, the system will populate the vendor in the item table.
  • Primary Source Location Code (max 20 chars): Required field. 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. This value may also be managed in DemandCaster. Required column.
  • Inventory or Non Inv. (bit; 0 (not stock) or 1 (stock)): Required field. This defines if an item is stocked or not stocked at the given location. Required column. Leave blank if item value used. ERP or DemandCaster can be source of master data.
  • Lead Time days (calendar days, int): Required field. 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.
  • Case Pack Quantity: Required field. 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.
  • Current Cost (each): Required field. 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.
  • Min Order Quantity (float): Required field. 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.
  • Safety Stock: Required field. 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.

3. Locations (locations.txt)

This is a required table.

These are physical warehouse and/or manufacturing locations and not inventory locations within a site. Even if there is one location being planned, this table with the one location identified is required. 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

4. Customers (customers.txt)

This is a required table.

The table columns are as follows. If implementing Advanced Business Planning 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 Advanced Business Planning 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.

5. Customer Orders (customerorders.txt)

This is a required table.

For multi-location companies with the DRP option, a sales order may be created against a source location to create demand. The associated replenishment order will be offset by an associated purchase 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): Characters are O = open and C = closed. If a line has been cancelled, please use the status C but leave the ship quantity and date blank. If a line is planned, please use the status O. 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. Please note that customer orders file should contain only status O or C. Statuses other than O and C will not be uploaded and thrown under “Not fixed error” with “Status does not exist” message.
  • 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): This is the location the item was shipped from. 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.

6. Product Classes (productclasses.txt)

This is a required table.

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.

7. Vendors (vendors.txt)

This is a required table.

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.

8. Vendor Orders (vendororders.txt)

This is a required table.

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.

This table may also be used for multi-location company transfer orders when the transfer is handled via a sales order and an accompanying purchase order by location. Please add all open and closed inter-company transfer orders in the vendororders.txt file.

If you are uploading only open purchase or transfer orders, you will still need to upload two blank columns for the actual delivery date and quantity. A quick solution is to add one closed order line in the file with actual delivery date and quantity. That will automatically add the columns for the rows that have a blank ship date and quantity.

Each upload deletes the prior vendor and, if applicable, transfer 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, In-Transit): Characters are O = open, C = closed, and P = in-transit purchase or transfer orders, and U = special code for in-transit (transfer orders only). Required field.
    • Status P:  As an option, purchase or transfer orders with a status "P" are considered In-transit orders. These orders will show up as green fonts in the replenishment orders column in the destinations items requirement plan. The green font color represents purchase orders or transfer orders that are already in transit from the supplier.
    • Transfer order status special logic: As an option, transfer orders with a status "U" are considered either planned or unfulfilled from the source and will always explode their requirements from the destination to the source. If the back-flushing setting is set to "Yes" at the destination, a transfer order with the status "O" or "P" will also explode its requirements to the source, otherwise if the back-flushing setting is set to "No," it will not explode.
  • 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. If the order is a transfer order, you may leave the vendor code or leave blank. 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): This is the location where the purchase or transfer order will be received. This is typically pulled from the vendor or transfer order header table. Required if DRP option is enabled.

9. Transfer Orders (transferorders.txt)

Applicable to companies who have a formal transfer order management system versus managing transfers via sales orders and purchase orders between locations/entities. Regardless, both types of transfers may be applied.

  1. Order Number and Line (max 20 chars):Order number is typically from the transfer order header table and the line is from the transfer order detail table. Required field.
  2. Status (1 char for Open, Closed, In-Transit): Characters are O = open, C = closed, and T = for in-transit. Required field.
    • Status "O" means the transfer order quantity is at both the source and destination and is not send yet.
    • Status "T" are considered in transit.
    • Status "C" means the transfer order is closed at both source and destination.
  3. Order Date (date):The date the transfer order was created. This is typically pulled from the transfer order header and is a required field. Not a required field.
  4. Item/Product Code (max 40 chars):This is the item that is being transferred. This is typically pulled from the transfer order detail table. Required field.
  5. Source Location (max 20 chars): This is the location where the item is being transferred from. This is typically pulled from the transfer order header. Required field.
  6. Destination Location Code (max 20 chars): This is the destination location where the transfer order will be received from. This is typically pulled from the transfer order header table.
  7. Quantity: This is the transfer order quantity that is either open, closed, or in transit.
  8. Ship Date (date): This is the actual ship date from the source
  9. Delivery Date (date): This is the scheduled delivery date at the destination or when already delivered the actual delivery date

10. Bill of Material (bommultilocation.txt)

Required for manufacturing companies 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 data.

  • 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.
  • E - Parent Item Operation Number (max 10 char; optional):  This is required for Dynamic Lead Time and Yield Loss Panning. This is the operation number on the parent item where the child item is consumed.
  • F - Percentage Yield Loss (double; optional): This is required for yield loss planning.  This is the yield percentage of the child item when it is consumed by the parent.
  • G - Fixed Yield Loss (double; optional): This is required for yield loss planning. This is the fixed yield loss of the child item when it is consumed by the parent.

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.

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;"

BOM Infinite Loop Issue

11. 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.

If you are uploading only open orders, you will still need to upload two blank columns for the actual delivery date and quantity. A quick solution is to add one closed order line in the file with actual delivery date and quantity. That will automatically add the columns for the rows that have a blank ship date and quantity.

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. A planned status (P) order will always explode to the components regardless if back-flushing is off ("No") or on ("Yes"). 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): 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.

12. 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): For multi-location companies. Ties the work center to a specific location if DRP is enabled.

13. 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): For multi-location companies. If the routing is tied to specific location otherwise leave blank.
  • I - Input Percentage Yield Loss (double; optional): This is required for yield loss planning. This is the yield percentage of the product at the input of the operation.
  • J - Input Fixed Yield Loss (double; optional): This is required for yield loss planning. This is the fixed yield loss of the product at the input of the operation.
  • K - Output Percentage Yield Loss (double; optional): This is required for yield loss planning. This is the yield percentage of the product at the output of the operation.
  • L - Output Fixed Yield Loss (double; optional): This is required for yield loss planning. This is the fixed yield loss of the product at the output of the operation.

14. 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.

If you are uploading only open orders, you will still need to upload two blank columns for the actual delivery date and quantity. A quick solution is to add one closed order line in the file with actual delivery date and quantity. That will automatically add the columns for the rows that have a blank ship date and quantity.

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. Not a required field. Leave blank if value defaults to routing value.
  • 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. Not a required field. Leave blank if value defaults to routing value.
  • J - Lead Time: This is the number of days required to process an outside operation. Not a required field. Leave blank if value defaults to routing value.

15. 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.

16. Item Tagging (itemtagsmultilocation.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 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
  • D - Tag Category: Optional not required field

17. Linking (productchainingmultilocation.txt)

Optional table

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

This is uploaded on demand only. There is no automation at this time. More detailed description is provided here: How to apply a mass supercession to many items at once (linking new items to old items).

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): Old item
  • B - Item Location: Old item location
  • C - Linked Item Code: New item location
  • D - Linked Item Location: New item
  • E - Linking Type: 0 (full linking) or 1 (forecast only). In case it is empty - assumed 0 (full linking)
  • F - Linking Date: Linking Start Date (not required)

18. Transition Linking (transitionlinking.txt)

Optional table

This is applicable to both finished goods and components. The temporary linking is most applicable to components. The table columns are as follows:

  • A - Item/Product Code: Old item
  • B - Linked Item/Product Code: New item
  • C - Location code
  • D - Parent Item Code: Parent item. Leave blank for finished goods linking
  • E - Type (0 for soft, 1 for hard; 2 for temporary)
  • F - Start Date 
  • G - 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.

Soft linking is not available for components.

19. 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

20. User Defined Forecast - Months (userdefinedforecastmonths.txt) - this is  applicable to Advanced Requirements Planning only

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

Optional table for Advanced Requirements Planning uploads

These are loaded to requirement planning forecasts when Advanced Business Planning 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 2 designates a location specific forecast.

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: 2 – Location specific
  • C - Loc. Code: The location the forecast is being applied.
  • 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

21. User Defined Forecast - Weeks (userdefinedforecast.txt) - this is applicable to Advanced Requirements Planning only

Optional table

These are loaded to requirement planning forecasts when Advanced Business Planning is not provisioned. This is uploaded on demand only. There is no automation at this time. Not a required 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: 2 – Location specific
  • C - Loc. Code: The location the forecast is being applied.
  • 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.

22. User Defined SOP Forecast (userdefinedsopforecast.txt) - this is applicable to Advanced Business Planning only

These files are loaded when Advanced Business Planning is provisioned. User Defined SOP Forecast includes sales input over a period of time starting a future bucket.

The easiest way on how to upload this file is to download your Demand Plan, which can serve as a template for your User Defined SOP Forecast file. Kindly note that deactivated items and items with no sales history would show as an error in Data Upload, error log section. Advanced Business Planning follows PCL structure, thus product, customer and location should be in the file.

Please find below a recommended template for userdefinedsopforecast.txt. Letters indicate an order and a name of a column.

  • A - Item Code
  • B - Location code
  • C - Location Name***
  • D - Customer code
  • E - Customer Name***
  • F - Bucket Type (M for month and W for weeks, depending on your method)
  • G - Week 1 Forecast (if today is 7th Nov 2022, your Forecast should start as of 14th Nov 2022) / Month 1 Forecast (if today is November, your Forecast should start as of December)
  • H - Week 2 Forecast / Month 2 Forecast
  • I - Week 3 Forecast / Month 3 Forecast
  • J - Week 4 Forecast / Month 4 Forecast
  • K - Week 5 Forecast / Month 5 Forecast
  • L - Week 6 Forecast / Month 6 Forecast
  • M - Week 7 Forecast / Month 7 Forecast
  • N - Week 8 Forecast / Month 8 Forecast
  • O - Week 9 Forecast / Month 9 Forecast
  • P - Week 10 Forecast / Month 10 Forecast
  • Q - Etc.

23. Alternate Supply Sources (alternatesources.txt)

Optional table

Alternate sources is a function that is included as part of multi-location (DRP) planning. The premise is to define 1 or more alternate locations for a given SKU. This data table may be used as part of the Alternate Supply/Source Requirement Planning functionality. Please note that 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

24. Containerization (containerizationcriteria.txt)

Optional table

See the article Batch Planning for more information.

This upload file sets the values used in the containerization for an item/SKU. In the example below, the values for Cost (0.75), Length (5), Weight (15), and Case cube (5) will be set to item 71 in location DC.

This is a non-destructive upload which means new or changes to existing templates may be uploaded. However, the values not filled in will be automatically set to zero.

The file is as follows:

  • A - Item Code. Required. Error if the item code does not exist.
  • B - Cost (required - but may be left blank)
  • C - Length (required - but may be left blank)
  • D - Weight (required - but may be left blank)
  • E - Cube (required - but may be left blank)
  • F - Location Code (mandatory when DRP enabled. Fixed error if location is deactivated. Error if location or item/location combo not available)

25. Batching Template (batchingtemplate.txt)

Optional table

See the article Batch Planning for more information.

This upload creates the templates used in batching. Each line is a specific batching template that includes its specific criteria.

All columns are required, however, columns may be left blank

This is a non-destructive upload which means new or changes to existing templates may be uploaded. However, values not filled in will be automatically set to zero.

The file is as follows:

  • A - Template Name:  Duplicate names are allowed.
  • B - Criteria Name: Duplicates disallowed. Must be a unique name.
  • C - Cost Min: Must be a number value.
  • D - Cost Max: Must be a number value. 
  • E - Units Min: Must be a number value. 
  • F - Units Max: Must be a number value.
  • G - Length Min: Must be a number value.
  • H - Length Max: Must be a number value.
  • I - Weight Min: Must be a number value.
  • J - Weight Max: Must be a number value.
  • K - Volume Min: Must be a number value.
  • L - Volume Max: Must be a number value.
  • M - Supplier Filter: Applicable supplier codes.  Each code will be separated by &&
  • N - Categories Filter: Applicable category codes. Each code will be separated by &&
  • O - Customers Filter: Applicable customer codes. Each code will be separated by &&
  • P - Tags Filter: Applicable tags.  Each tag will be separated by &&
  • Q - Locations Filter: Applicable location codes. Each code will be separated by &&
  • R - Search Filter: Text to search for in the product code or description up to 200 symbols

26. Co-Product Template (coproducttemplate.txt)

Optional table

See the article Co-Product Planning (Multi-Out) for more information.

This upload creates the templates used in co-product planning. Each line is a specific batching template that includes its specific criteria.

All columns are required, however, columns may be left blank

This is a non-destructive upload which means new or changes to existing templates may be uploaded. However, values not filled in will be automatically set to zero.

The file is as follows:

  • A - Template name: Must be unique. Error if the field is empty.
  • B - Item code: Item code to be included in the template. Error if the item code does not exist.
  • C - Multiple: Number of the specific item to be produced in each run. Error if the value is not a number.
  • D - Is Primary: Accepts "true", "false" or number. Any number greater than zero or string "true" is True. Everything else is False and a non-fixed error will be shown.
  • E - Location: If the business node is DRP the Location Code is required. Error if the location is deactivated or if the location code cannot be found in the system.

In the above example, items 71, 30011, and 30060 will become part of template "Template 1" with the corresponding multiples. If there is an existing template with the same name, it will be replaced.

"Template 1" will be primary for items 71 and 30011, but not for 30060.

27. 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.

28. 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

29. Base entities associations (baseEntityAssociation.txt)

Applicable to Advanced Business Planning only. This is related to instances where an item will be sold to customers that have not previously purchased an item or a new item is introduced with no prior sales.

For companies with multi-location enabled:

Column A: Item (Item Code)
Column B: Customer (Customer Code)
Column C: Location (Location Code)
*Column D: Remove (defines if the association should be added or removed- 0 or empty for association, 1 for disassociation)

For companies without multi-location enabled:

Column A: Item (Item Code)
Column B: Customer (Customer Code)
*Column C: Remove (defines if the association should be added or removed- 0 or empty for association, 1 for disassociation)

The Remove column is optional. If not included, the association will be added.

30. Manual Issued Quantity (manualissuedquantity.txt)

Optional Requirement Planning table

Please read the Accounting for Manually Issue Material in the Requirement Plan article to learn about the usage of this data table  

The table has the following columns:

  • Column A: Shop Order Number (Production Order Number) this is the production order that the material was issued to
  • Column B: Item (Item Code) this is the item code of the material that was issued to the production order
  • Column C: Operation Number (Operation Code) this is the operation where the material was consumed.  This should match the Parent Item Operation Number on the BOM.
  • Column D: Quantity (Quantity) this is the total quantity of the material that has been manually issued to the production order at the operation
  • Column E: Location (Location Code)

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.