Data Mapping

In this chapter you can find information about raw layer mapping in the system for STG2.

The following tables need to be mapped into the RAW layer for Inventory to be set up. With the release a standard data mapping into the raw layer is provided from the Navision erp tables. For other data sources either the customer should provide data directly into the raw layer tables er you use the raw views for data mapping.

raw.LOCATION

Stores and warehouses, all locations that have stock transactions.
Primary key: NO

  • NO (nvarchar) - required: The location’s number
  • NAME (nvarchar) - required: the name of the location
  • GROUP (nvarchar) - not required: Group of the Locations. Not used in standard the Inventory Module standard data mapping
  • TYPE (nvarchar) - required: ‘ST’ for stores ans ‘WH’ for warehouses
  • CLOSED(bit) - required: 1 if the location is closed; 0 otherwise
  • OPENING_DATE (date) - not required: not used in the Inventory Module standard data mapping
  • CLOSING_DATE (date) - not required: not used in the Inventory Module standard data mapping

raw.VENDOR

A list of all vendors that the company is in business with.
Primary key: NO

  • NO (nvarchar) - required: The vendor’s number
  • NAME (nvarchar) - required: the name of the vendor
  • LEAD_TIME (int) - not required: The number of days that is the estimated time from when an order is placed until it arrives MISSING more detail about lead time mapping
  • CLOSED(bit) - required: 1 if the company is no longer in buisness with the vendor; 0 otherwise

raw.PRODUCT_INFO

Product information down to product level (model, colour, size). In the examples below we provide examples of the most complicated types of product hirarchy’s but for many (most) setups the NO,PRODUCT_NO and ARTICLE_NO would all be populated with the same information. Primary key: NO

  • NO (nvarchar) - required: The number representing the deepest level in the product hirarchy (before going to location level). Example: We could have Item 1234 in red (colour number 3), Size S and style long - 1234_03_S_Long
  • NAME (nvarchar) - required: The name of the product on the deepest level. Example: Red Sparkling Red Sweater Long Sleeved Small
  • ARTICLE_NO (nvarchar) - required: The number of the article. The article level is a product level between product info and product and can be used to plan on. This is often a requirement in fashion buisnesses referred to as option planning. Example: 1234_03
  • ARTICLE_NAME (nvarchar) - required: The name of the article. The article level is a product level between product info and product and can be used to plan on. This is often a requirement in fashion buisnesses referred to as option planning. Example: Red Sparkling Sweater
  • PRODUCT_NO(nvarchar) - required: the product number for the item without any variants. Example: 1234
  • PRODUCT_NAME (nvarchar) - required: the product name of the item without any variants. Example: Sparkling Sweater
  • SIZE_NO (nvarchar) - not required: Size number, if you populate this field it also needs to exist in raw.PRODUCT_VARIANT_SIZE
  • COLOUR_NO (nvarchar) - not required: Colour number, if you populate this field it also needs to exist in raw.PRODUCT_VARIANT_COLOUR
  • STYLE_NO (nvarchar) - not required: Style number, if you populate this field it also needs to exist in raw.PRODUCT_VARIANT_STYLE
  • DESCRIPTION (nvarchar) - not required: description of the product
  • PRIMARY_VENDOR_NO (nvarchar) - not required: The Primary Vendors number (see raw.VENDOR)
  • PV_LEAD_TIME_DAYS (smallint) - not required: The number of days that is the estimated time from when an order is placed from the primary vendor of this product until it arrives. If not populated it is set to the default = 1. Can be overridden in raw.PRODUCT_PURCHASE_INFO or raw.PRODUCT_TRANSFER_INFO
  • PV_ORDER_FREQUENCY_DAYS (smallint) - not required: The number of days between orders of this product. Can be overridden in raw.PRODUCT_PURCHASE_INFO or raw.PRODUCT_TRANSFER_INFO.
  • PV_MIN_ORDER_QTY (int) - not required: Minimum order quantity for each order of this product from the primary vendor. If not populated it is set to the default = 1. Can be overridden in raw.PRODUCT_PURCHASE_INFO or raw.PRODUCT_TRANSFER_INFO.
  • ORIGINAL_NO (nvarchar) - not required: Vendor’s Product Number
  • CLOSED(bit) - required: 1 if the product is closed; 0 otherwise. If closed then the product is not replenished in AGR. The closed items can however be available for reporting
  • RESPONSIBLE (nvarchar) - not required: Person responsible for the product. Can also be overridden on SKU level by raw.PRODUCT_ASSORTMENT
  • SALE_PRICE (decimal) - not required: Original selling price
  • COST_PRICE (decimal) - not required: The product’s cost price (Landed cost price). Can be overridden in raw.PRODUCT_PURCHASE_INFO or raw.PRODUCT_TRANSFER_INFO
  • ORDER_MULTIPLE (decimal) - not required: Quantity per unit, orders will be made in multiples of this number. If not populated it is set to the default = 1. Can be overridden in raw.PRODUCT_PURCHASE_INFO or raw.PRODUCT_TRANSFER_INFO
  • VOLUME (nvarchar) - not required: The volume of one unit. If not populated it is set to the default = 1.
  • WEIGHT (nvarchar) - not required: The volume of one unit. If not populated it is set to the default = 1.
  • MIN_STOCK (nvarchar) - not required: if greater then 0 then the system will make sure stock doesn’t go below the min value
  • MAX_STOCK (nvarchar) - not required: only required when using min/max replenishment method.
  • TERMINAL_DATE (nvarchar) - not required: not used in the Inventory Module standard data mapping
  • QTY_PALLET (nvarchar) - not required: The number of units in one pallet. If not populated it is set to the default = 1. Can be overridden in raw.PRODUCT_PURCHASE_INFO or raw.PRODUCT_TRANSFER_INFO
  • QTY_PALLET_LAYER (nvarchar) - not required: The number of units in one layer in a pallet. If not populated it is set to the default = 1. Can be overridden in raw.PRODUCT_PURCHASE_INFO or raw.PRODUCT_TRANSFER_INFO. Can be overridden in raw.PRODUCT_ASSORTMENT or raw.PRODUCT_TRANSFER_INFO
  • PRODUCT_GROUP_NO_LVL_1 (nvarchar) - required: The no of the highest level in the product group hierarchy (seeraw.PRODUCT_GROUP)
  • PRODUCT_GROUP_NO_LVL_2 (nvarchar) - not required: The no of the next highest level in the product group hierarchy (seeraw.PRODUCT_GROUP)
  • PRODUCT_GROUP_NO_LVL_3 (nvarchar) - not required: The no of the 3rd level in the product group hierarchy (seeraw.PRODUCT_GROUP)
  • PRODUCT_GROUP_NO_LVL_4 (nvarchar) - not required: The no of the 4th level in the product group hierarchy (seeraw.PRODUCT_GROUP)
  • PRODUCT_GROUP_NO_LVL_5 (nvarchar) - not required: The no of the 5th level in the product group hierarchy (seeraw.PRODUCT_GROUP)
  • PRODUCT_GROUP_NO_LVL_6 (nvarchar) - not required: The no of the 6th level in the product group hierarchy (seeraw.PRODUCT_GROUP)
  • PRODUCT_GROUP_NO_LVL_7 (nvarchar) - not required: The no of the 7th level in the product group hierarchy (seeraw.PRODUCT_GROUP)
  • PRODUCT_GROUP_NO_LVL_8 (nvarchar) - not required: The no of the 8th level in the product group hierarchy (seeraw.PRODUCT_GROUP)
  • PRODUCT_GROUP_NO_LVL_9 (nvarchar) - not required: The no of the 9th level in the product group hierarchy (seeraw.PRODUCT_GROUP)
  • PRODUCT_GROUP_NO_LVL_10 (nvarchar) - not required: The no of the 10th level in the product group hierarchy (seeraw.PRODUCT_GROUP)

raw.PRODUCT_PURCHASE_INFO

It is not necessary to populate this table but if it is populated it will override the purchase info from [raw.PRODUCT_INFO] for orders from vendors.
Primary key: PRODUCT_ITEM_NO,LOCATION_NO,VENDOR_NO

  • PRODUCT_ITEM_NO (nvarchar) - required: The Item that is being received. (links to the NO column in raw.PRODUCT_INFO)
  • LOCATION_NO (nvarchar) - required: The number of the internal location that receives the purchase order (see raw.LOCATION)
  • VENDOR_NO (nvarchar) - required: The number of the vendor that will deliver the purchase order (see raw.vendor)
  • PRIMARY (bit) - required: 1 if this represents the primary order route; 0 otherwise. Only one line can have primary = 1 for each combination of PRODUCT_ITEM_NO and LOCATION_NO in both raw.PRODUCT_PURCHASE_INFO and raw.PRODUCT_TRANSFER_INFO
  • LEAD_TIME_DAYS (smallint) - not required: The number of days that is the estimated time from when an order is placed from the primary vendor of this product until it arrives.
  • ORDER_FREQUENCY_DAYS (smallint) - not required: The number of days between orders of this product from the vendor.
  • MIN_ORDER_QTY (decimal) - not required: Minimum order quantity for each order of this product from the vendor.
  • COST_PRICE (decimal) - not required: The cost of the item when ordering from this vendor in the base currency
  • ORDER_MULTIPLE (decimal) - not required: Quantity per unit, orders will be made in multiples of this number.
  • QTY_PALLET (nvarchar) - not required: The number of units in one pallet
  • QTY_PALLET_LAYER (nvarchar) - not required: The number of units in one layer in a pallet

raw.PRODUCT_TRANSFER_INFO

It is not necessary to populate this table but if it is populated it will override the purchase info from [raw.PRODUCT_INFO] for transfer orders between internal locations. Primary key: PRODUCT_ITEM_NO,LOCATION_NO,TRANSFER_FROM_LOCATION_NO

  • PRODUCT_ITEM_NO (nvarchar) - required: The Item that is being received. (links to the NO column in raw.PRODUCT_INFO)
  • LOCATION_NO (nvarchar) - required: The number of the internal location that receives the transfer order (see raw.LOCATION)
  • TRANSFER_FROM_LOCATION_NO (nvarchar) - required: The number of the internal location the item is transferred from (see raw.LOCATION)
  • PRIMARY (bit) - required: 1 if this represents the primary order route; 0 otherwise. Only one line can have primary = 1 for each combination of PRODUCT_ITEM_NO and LOCATION_NO in both raw.PRODUCT_PURCHASE_INFO and raw.PRODUCT_TRANSFER_INFO
  • LEAD_TIME_DAYS (int) - not required: The number of days that is the estimated for the product to be transferred between locations
  • ORDER_FREQUENCY_DAYS (smallint) - not required: The number of days between orders of this product from the transfer from location.
  • MIN_ORDER_QTY (decimal) - not required: Minimum order quantity for each transfer order of this product
  • COST_PRICE (decimal) - not required: The cost of the item when it is transferred between these locations (usually not populated)
  • ORDER_MULTIPLE (decimal) - not required: Quantity per unit, transfer orders will be made in multiples of this number.
  • QTY_PALLET (nvarchar) - not required: The number of units in one pallet
  • QTY_PALLET_LAYER (nvarchar) - not required: The number of units in one layer in a pallet

raw.PRODUCT_ASSORTMENT

If this table is not populated then all products are open on all internal locations. Primary key: PRODUCT_ITEM_NO,LOCATION_NO

  • PRODUCT_ITEM_NO (nvarchar) - required: The Item that is being received. (links to the NO column in raw.PRODUCT_INFO)
  • LOCATION_NO (nvarchar) - required: The number of the location (seeraw.LOCATION)
  • CLOSED(bit) - required: 1 if the product is closed in this particular location; 0 otherwise
  • MIN_STOCK (nvarchar) - not required: MISSING
  • MAX_STOCK (nvarchar) - not required: MISSING
  • RESPONSIBLE (nvarchar) - not required: Person responsible for the product. This is an SKU level override of the same column in raw.PRODUCT_INFO

raw.PRODUCT_GROUP

A flat list of all Product Groups independent of their level in the product group hirarchy. Hirarchy of Product Groups is determened from raw.PRODUCT_INFO Primary key: NO

  • NO (nvarchar) - required: The Product Group number
  • NAME (nvarchar) - required: The name of the Product Group

raw.PRODUCT_VARIANT_COLOUR

A list of all product colours. Only needs to be populated if you populate the colour field in raw.product_info. Primary key: NO

  • NO (nvarchar) - required: The colour number
  • NAME (nvarchar) - required: The name of the colours
  • DESCRIPTION (nvarchar) - not required: The description of the colours

raw.PRODUCT_VARIANT_SIZE

A list of all product sizes. Only needs to be populated if you populate the size field in raw.product_info. Primary key: NO

  • NO (nvarchar) - required: The size number
  • NAME (nvarchar) - required: The name of the size
  • DESCRIPTION (nvarchar) - not required: The description of the size

raw.PRODUCT_VARIANT_STYLE

A list of all product styles.Only needs to be populated if you populate the style field in raw.product_info. Primary key: NO

  • NO (nvarchar) - required: The style number
  • NAME (nvarchar) - required: The name of the style
  • DESCRIPTION (nvarchar) - not required: The description of the style

raw.PURCHASE_ORDER

A list of all purchase orders in the ERP system. The table can both include both orders on the way (undelivered) and orders already delivered but for the standard inventory data mapping undelivered are the only ones that are required Primary key: NO

  • NO (nvarchar) - required: Purchase order number
  • ORIGINAL_PO_NO (nvarchar) - not required: Original Purchase Order number (only for information). Not used in standard Inventory datamapping
  • LOCATION_NO (nvarchar) - required: The number of the internal location that receives the purchase order (see raw.LOCATION)
  • VENDOR_NO (nvarchar) - required: The number of the vendor that will deliver the purchase order (see raw.vendor)
  • DELIVERY_DATE (date) - not required: The date that the order is planned to be received
  • DELIV_STATUS (int) - required: 1 if all the items in the purchase order have been received; 0 otherwise

raw.PURCHASE_ORDER_LINE

A list of all purchase orders in the ERP system. The table can both include both orders on the way (undelivered) and orders already delivered but for the standard inventory data mapping undelivered are the only ones that are required Primary key: PURCHASE_ORDER_NO, PRODUCT_ITEM_NO

  • PURCHASE_ORDER_NO (nvarchar) - required: Purchase order number (seeraw.PURCHASE_ORDER)
  • PRODUCT_ITEM_NO (nvarchar) - required: The Item that is being received (links to the NO column in raw.PRODUCT_INFO)
  • QUANTITY (decimal) - required: the estimated amount that will be received
  • DELIVERY_DATE (date) - not required: The date that the orderline is planned to be received
  • EXPIRE_DATE (date) - not required: The date that the the item will expire
  • DELIV_STATUS (int) - required: 1 if the item has been received; 0 otherwise

raw.TRANSFER_ORDER

A list of all transfer orders in the ERP system. The table can both include both orders on the way (undelivered) and orders already delivered but for the standard inventory data mapping undelivered are the only ones that are required Primary key: NO

  • NO (nvarchar) - required: Transfer order number
  • ORIGINAL_TO_NO (nvarchar) - not required: Original Transfer Order number (only for information). Not used in standard Inventory datamapping
  • LOCATION_NO (nvarchar) - required: The number of the internal location that receives the transfer order (see raw.LOCATION)
  • ORDER_FROM_LOCATION_NO (nvarchar) - required: The number of the location that will deliver the transfer order (see raw.LOCATION)
  • DELIVERY_DATE (date) - not required: The date that the order is planned to be received
  • DELIV_STATUS (int) - required: 1 if all the items in the transfer order have been received; 0 otherwise

raw.TRANSFER_ORDER_LINE

A list of all transfer orders in the ERP system. The table can both include both orders on the way (undelivered) and orders already delivered but for the standard inventory data mapping undelivered are the only ones that are required Primary key: TRANSFER_ORDER_NO, PRODUCT_ITEM_NO

  • TRANSFER_ORDER_NO (nvarchar) - required: Transfer order number (seeraw.TRANSFER_ORDER)
  • PRODUCT_ITEM_NO (nvarchar) - required: The Item that is being received. (links to the NO column in raw.PRODUCT_INFO)
  • QUANTITY (decimal) - required: the estimated amount that will be received
  • DELIVERY_DATE (date) - not required: The date that the orderline is planned to be received
  • EXPIRE_DATE (date) - not required: The date that the the item will expire
  • DELIV_STATUS (int) - required: 1 if the item has been received; 0 otherwise

raw.STOCK_LEVEL

this table contains information about the stock level for each item . Primary key: LOCATION_NO, PRODUCT_ITEM_NO,EXPIRE_DATE

  • PRODUCT_ITEM_NO (nvarchar) - required: The item the stock belongs to. Links to the NO column in raw.PRODUCT_INFO
  • LOCATION_NO (nvarchar) - required: the location where the stock is held. See raw.LOCATION
  • EXPIRE_DATE (date) - required: The date the stock will expire. Since EXPIRE_DATE is part of the primary key this column must be populated. If the stock doesn’t expire, populate the field with 2100-01-01
  • STOCK_UNITS (decimal) - required: The total quantity currently in stock of a given item in the given location

raw.SALE_HISTORY

this table contains all historical sales unit information. For qualaty forecasting we recommend haveing at least 3 years of sale history. Primary key: LOCATION_NO, PRODUCT_ITEM_NO,DATE

  • PRODUCT_ITEM_NO (nvarchar) - required: The Item that is being sold. Links to the NO column in raw.PRODUCT_INFO
  • LOCATION_NO (nvarchar) - required: the location where the item is sold. See raw.LOCATION
  • DATE (date) - required: The actual date of the sale transaction
  • SALE (decimal) - required: The total quantity sold of a given item in the given location on this particular date. (Sum of all pose transactions/sales on this particular date)
  • TRANSFER (decimal) - not required: If transfers between internal locations should be used as sales then they should be populated here. Read more about this in Location Setup
  • MARKDOWN_SALE (decimal) - not required: Not used in standard Inventory data mapping

raw.STOCK_HISTORY

this table contains information about the stock history. It can either been populated with the stock history or with transactional information and the data mapping will build up the stock history for you. Primary key: LOCATION_NO, PRODUCT_ITEM_NO,EXPIRE_DATE

  • PRODUCT_ITEM_NO (nvarchar) - required: The item the stock belongs to. Links to the NO column in raw.PRODUCT_INFO
  • LOCATION_NO (nvarchar) - required: the location where the stock is held. See raw.LOCATION
  • DATE (date) - required: The date of the stock level or when the stock move occured
  • STOCK_MOVE (decimal) - not required: The amount of stock moved on the given date.
  • STOCK_LEVEL (decimal) - not required: The total quantity currently in stock of a given item in the given location in the given date

raw.BOM_COMPONENT

this table contains information product bill of material (recipe). It’s not required to populate this table unless you want to utilise BOM functionality Primary key: PRODUCT_ITEM_NO, COMPONENT_PRODUCT_ITEM_NO

  • PRODUCT_ITEM_NO (nvarchar) - required: Links to the NO column in raw.PRODUCT_INFO
  • COMPONENT_PRODUCT_ITEM_NO (nvarchar) - required: The components Item number. Links to the PRODUCT_NO column in raw.PRODUCT_INFO
  • QUANTITY (decimal) - required: how many pieces of the component is needed in this product