Specification of Integration

Owned by Sergey Schiller (Unlicensed)
Last updated: May 03, 2023 by Dmitry Dyachenko
20 min read

The attached excel file contains a list of tables with columns description for each function block data load. It also highlights required and optional tables/fields that are only used when certain features are enabled.

All tables are separated into blocks, just like in the user interface.

Overview

  1. The MySales system works with a wide range of databases (Oracle, Microsoft SQL Server, MySQL, PostgreeSQL, Oracle Teradata and etc.).

  2. We recommend that you choose the database that matches the database of your ERP or similar system where you already have data on sales, stock balances and which is used to register purchase orders and goods receipts.

  3. You can use the following options to create a database:

    1. Database instance on separate server

    2. Separate database instance on the same server as the ERP database instance.

    3. Separate database on ERP server database instance (recommended).

    4. Separate database schema on same database on ERP server database instance.

  4. The main workload usually occurs at night, when sales and stock balance data are exchanged between MySales and ERP system and forecasts and orders calculated.

If you are tackle with difficulties when preparing data for loading or with performance issues, you can always contact us, but you must know the data structure of your ERP system.

We may change the data structure, lengths and types of some fields for data storage optimization and compatibility purposes.

Not all tables are required for integration. The list of tables is determined by the capabilities of the ERP system used and the customer's processes.

Data types

The following data types are used in the MySales system:

Data type

MSSQL

ORACLE

MySQL

PostgeeSQL

Data type

MSSQL

ORACLE

MySQL

PostgeeSQL

Integer 4 Bytes

-2,147,483,648 to 2,147,483,647

int

number(10)

int

integer

Integer 2 Bytes

-32,768 to 32,767

smallint

number(5)

smallint

smallint

1 Byte

0 to 255

tinyint

number(3)
number(1)

smallint

smallint

identity insert

IDENTITY

SEQUENCE

AUTO_INCREMENT

serial (integer)
smallserial (smallint)

ascii string with fixed length

char(*)

char(*)

char(*) character set latin1

char(*)

UNICODE string with fixed length

nchar

nchar(*)

char(*) character set utf8mb4

char(*)

ascii string with variable length

varchar(*)

varchar2(*)

varchar(*) character set latin1

varchar(*)

UNICODE string with variable length

nvarchar(*)

nvarchar2(*)

varchar(*) character set utf8mb4

varchar(*)

Date

date

date

date

date

Date and time (DB server time)

datetime

timestamp

datetime

timestamp without timezone

Decimal number with p length and s precision

numeric(p,s)

number(p,s)

numeric(p,s)

numeric(p,s)

ETL process example

Extract, Transform, Load (ETL)

Here is an example of the integration process between the ERP system and the MySales system:

 

Master data

Below is an example of data exchange for STORES table, which contains a list of stores and warehouses and using an Microsoft SQL Server as database.

The STORES table uses the STORE_ID primary key with type smallint (int) as the store/warehouse identifier.

If the ERP system uses a similar table to store the list of stores and warehouses, and they have the same master key type, you don't need to use STORES.CLIENT_CODE field.

If the ERP system uses a different Primary Key or uses separate tables for Stores and Warehouses you need to use STORES.CLIENT_CODE field:

  1. Set the IDENTITY properties for the STORE.STORE_ID field (auto-increment, sequence in other databases) so that an ID is automatically created for inserted records.

  2. Change the field type STORES.CLIENT_CODE to the type used in the same ERP system table and use this field for subsequent updates and inserting data in other tables such as assortment matrix, sales or stock balances.

  3. If the different tables is used for Stores and Warehouses in ERP with a different key fields, use one of two options:

    • Combine value ‘S'+ERP_STORE_ID and 'W’ + ERP_WHS_ID for STORES.CLIENT_CODE field.

    • Add second field STORES.CLIENT_CODE_W for warehouse correspondence.

Here is an example for MSSQL database, but you can use same technics for other databases:
Similar ERP table is used, with primary key type int that not exceed smallint value.

merge into msls.STORES as T with (tablock) from DbLink.ERPDB.dbo.MASTER_STORES as S with (nolock) on T.STORE_ID = S.ID when matched and ( ((T.STORE_CODE <> S.STORE_NUMBER) or (T.STORE_CODE is NULL and S.STORE_NUMBER is NOT NULL) or (T.STORE_CODE is NOT NULL and S.STORE_NUMBER is NULL)) or ((T.STORE_NAME <> S.NAME) or (T.STORE_NAME is NULL and S.NAME is NOT NULL) or (T.STORE_NAME is NOT NULL and S.NAME is NULL)) -- or ( other_fields_comparison ) then update set T.STORE_CODE = S.STORE_NUMBER, T.STORE_NAME = S.NAME -- other_fields when not matched by target then insert (STORE_ID, STORE_CODE, STORE_NAME) values (S.ID, S.NUMBER, S.NAME);

Similar ERP table is used, with primary key type GUID (binary(16)). STORES.STORE_ID is IDENTITY field.

merge into msls.STORES as T with (tablock) from DbLink.ERPDB.dbo.MASTER_STORES as S with (nolock) on T.CLIENT_CODE= S.ID when matched and ( ((T.STORE_CODE <> S.STORE_NUMBER) or (T.STORE_CODE is NULL and S.STORE_NUMBER is NOT NULL) or (T.STORE_CODE is NOT NULL and S.STORE_NUMBER is NULL)) or ((T.STORE_NAME <> S.NAME) or (T.STORE_NAME is NULL and S.NAME is NOT NULL) or (T.STORE_NAME is NOT NULL and S.NAME is NULL)) -- or ( other_fields_comparison ) then update set T.STORE_CODE = S.STORE_NUMBER, T.STORE_NAME = S.NAME -- other_fields when not matched by target then insert (STORE_CODE, STORE_NAME, CLIENT_CODE) values (S.NUMBER, S.NAME, S.ID);

Two different ERP table are used for Stores and Warehouses. STORES.STORE_ID is IDENTITY field.

merge into msls.STORES as T with (tablock) from ( select concat('S', ID) as CLIENT_CODE, STORE_NUMBER as CODE, NAME from DbLink.ERPDB.dbo.MASTER_STORES with (nolock) union all select concat('W', ID) as ID, concat('W', ID) as CODE, NAME from DbLink.ERPDB.dbo.MASTER_WAREHOUSES with (nolock) ) as S with (nolock) on T.CLIENT_CODE= S.CLIENT_CODE when matched and ( ((T.STORE_CODE <> S.CODE) or (T.STORE_CODE is NULL and S.CODE is NOT NULL) or (T.STORE_CODE is NOT NULL and S.CODE is NULL)) or ((T.STORE_NAME <> S.NAME) or (T.STORE_NAME is NULL and S.NAME is NOT NULL) or (T.STORE_NAME is NOT NULL and S.NAME is NULL)) -- or ( other_fields_comparison ) then update set T.STORE_CODE = S.STORE_NUMBER, T.STORE_NAME = S.NAME -- other_fields when not matched by target then insert (STORE_CODE, STORE_NAME, CLIENT_CODE) values (S.CODE, S.NAME, S.CLIENT_CODE);

General data

Below is an example of data exchange for SKU_RANGE_CURRENT table, which contains current assortment matrix data using delete + insert statement:

Sales and stock data

Usually, ERP systems use different tables to store stock balances and sales, goods receipts, but we need to combine all this data to save them in a single final table SALES.

 

DAILY_SALES (SALES_DATE, STORE_IDENT, PRODUCT_ID, SALES_QUANTITY, RETURN_QUANTITY, TOTAL_AMOUNT, DISCOUNT)
INVENTORY_BALANCE (BALANCE_DATE, STORE_IDENT, PRODUCT_ID, QUANTITY)
INVENTORY_GOODS_RECEIPTS (RECEIPT_NUMBER, RECEIPT_DATE, REF_ID, STORE_IDENT, PRODUCT_ID, QUANTITY, AMOUNT)

Forecast Tables

Master data

PRICELINES - Price lines (price lists)

Data update period: daily merge without deletion

This table is only filled if the prices in the ERP system assigned to a several of stores, and not by each store separately.

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

PRICELINE_ID

smallint

NOT NULL

Y

Price number

NAME

nvarchar(256)

NULL

 

Name

UPDATED

datetime

NULL

 

Update date

CLIENT_CODE

*

*

*

The priceline ID in the client ERP system, if it is not compatible with the smallint type.

Used to transfer data between systems.

REGIONS - Regions

Data update period: daily merge without deletion

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

REGION_ID

smallint

NOT NULL

Y

Region number

REGION_NAME

nvarchar(256)

NULL

Y

Region name

CLIENT_CODE

*

*

*

The region ID in the client ERP system, if it is not compatible with the smallint type.

Used to transfer data between systems.

STORES - Stores (and warehouses)

Data update period: daily merge without deletion

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

STORE_ID

smallint

NOT NULL

Y

Store number. It is desirable that the number that the user sees is filled in here

STORE_NAME

nvarchar(256)

NOT NULL

Y

Store name

REGION_ID

smallint

NOT NULL

Y

Region number from the REGIONS table

PRICELINE_ID

smallint

NULL

 

Price serial number from the PRICELINES table

OPEN_DATE

date

NULL

 

Store opening date

CLOSE_DATE

date

NULL

 

Store closing date

STORE_CODE

nvarchar(32)

NULL

 

Store code from the accounting system. Filled in only if the STORE_ID field is generated and does not match the number that the user sees.

ADDRESS

nvarchar(256)

NULL

 

Store Address

UPDATED

datetime

NULL

 

Update date

WHS

tinyint

NULL

 

Warehouse sign. Filled in as 1 for warehouses. Stores must be NULL. The value 0 is not allowed.

FORMAT_ID

tinyint

NULL

 

Store Format Id

FORMAT_CODE

char(1)

NULL

 

Store Format Code (e.g. Mini, Super, Hyper, Warehouse)

CLIENT_CODE

*

*

*

The store ID / warehouse ID in the client ERP system, if it is not compatible with the smallint type.

If different table in ERP system used, use a prefix, such as S for stores (S45) and W for warehouses (W2).

Used to transfer data between systems.

GROUPS - Product groups

Data update period: daily merge without deletion

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

GROUP_ID

smallint

NOT NULL

Y

Group number

GROUP_NAME

nvarchar(256)

NULL

Y

The name of the group of goods. Unique.

GROUP_CODE

nvarchar(32)

NULL

 

Group code from accounting system

PARENT_GROUP_ID

smallint

NULL

 

Paternal group number (for building hierarchies)

UPLIFT_WO_DISC

number(12, 2)

NULL

 

Filled in on the MySales side

UPLIFT_WITH_DISC

number(12, 2)

NULL

 

Filled in on the MySales side

AVG_BENEFIT

number(8, 2)

NULL

 

Filled in on the MySales side

AVG_ITEM_WIDTH

number(10, 2)

NULL

 

Filled in on the MySales side

UPDATED

datetime

NULL

 

Update date

CLIENT_CODE

*

*

*

The group ID in the client ERP system, if it is not compatible with the smallint type.

Used to transfer data between systems.

PRODUCTS - Products (SKU - Stock Keeping Units)

Data update period: daily merge without deletion

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier

GROUP_ID

smallint

NOT NULL

Y

Group number from the GROUPS table. SKUs are tied to the lowest level of the product hierarchy.

ARTICLE_NAME

nvarchar(256)

NOT NULL

Y

Name of product. Unique

ITEM_CODE

nvarchar(20)

NULL

 

Item code from the accounting system. Filled in only if the SKU_ID field is generated and does not match the number that the user sees

SUPPLIER_ID

smallint

NULL

 

Supplier identifier from SUPPLIERS table.
()

LAST_PURCHASE_PRICE

number(10, 2)

NULL

 

Purchase price WITHOUT value added tax

LAST_SALES_WEEK

number(10, 2)

NULL

 

Filled in on the MySales side

UPLIFT_WO_DISC

number(10, 2)

NULL

 

Filled in on the MySales side

UPLIFT_WITH_DISC

number(10, 2)

NULL

 

Filled in on the MySales side

AVG_BENEFIT

number(10, 2)

NULL

 

Filled in on the MySales side

SHELF_DAYS

smallint

NULL

Required for Replenishment

Total expiration period, in days.

(Expiration date minus production date)

PACKAGE_CONTENT

number(10, 3)

NULL

Only for Reorder Point

Multiplicity of orders for DC

MIN_ORDER_QTY

number(10, 3)

NULL

Only for Reorder Point

Minimum order quantity

PRODUCTS_UOM

nvarchar(10)

NULL

 

unit of measurement

UPDATED

datetime

NULL

 

insert or update date

VAT_PERCENT

number(5, 2)

NULL

Only for Price Optimisation

% vat on value added purchases. If %=0 then put 0

WEIGHT

number(10, 3)

NULL

Only for Replenishment

Product weight, kg

VOLUME

number(10, 3)

NULL

 

Product volume, l

HEIGHT

number(6, 2)

NULL

 

Product height, см

WIDTH

number(6, 2)

NULL

 

Product width, cm

DEPTH

number(6, 2)

NULL

 

Product length, cm

CLIENT_CODE

*

*

*

The product ID in the client ERP system, if it is not compatible with the int type.
Used to transfer data between systems.

General data

PRICES - Retail prices

Data update period: daily

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

STORE_ID/ PRICELINE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID /
Price identifier from PRICELINES.PRICELINE_ID

START_DATE

date

NOT NULL

Y

Price start date

END_DATE

date

NULL

 

Price end date. If the price is active, it should be NULL, or the year 2050. When a new price appears, the date of the last day when it was active must be put on the old price

PRICE

number(10, 2)

NOT NULL

Y

Price with Value Added Tax

PRICE_TYPE

varchar(20)

NULL

 

Price type (Promotion / Sale, etc.). Recommended to fill.

PRODUCTS_EAN - Barcodes (EAN codes) of products

Data update period: daily delete + insert, daily merge

Only the European Article Number (EAN) is entered in this table. Barcodes generated by the customer do not need to be uploaded here. The barcode data is used to send orders to suppliers and to search for prices on the Internet.

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

EAN

varchar(20)

NOT NULL

Y

Product barcode

Assortment matrices

SKU_RANGE - Assortment matrices, history and future data

Data update period: daily delete + insert, update

There are two approaches: if there is a history of assortment matrices in the client's system, and if it is absent.
If available, it is recommended to immediately fill in the SKU_RANGE table with the entire history. If not, it is recommended to fill in the SKU_RANGE_CURRENT table, and then run a query to fill in SKU_RANGE and accumulate history.

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

START_DATE

date

NOT NULL

Y

Starting date of entry into the assortment

END_DATE

date

NULL

 

Date of withdrawal from the range. If currently active, then the value should be empty (NULL)

UPDATED

datetime

NULL

 

Update date

SKU_RANGE_CURRENT - Current state of the assortment matrix

Data update period: daily truncate + insert, daily merge

If not, it is recommended to fill in the SKU_RANGE_CURRENT table, and then run a query to fill in SKU_RANGE and accumulate history.

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

Sales and stock data

SALES_SKU_DAY - Daily sales and balances

Data update period: daily delete + insert, taking into account the last days for which there may be significant changes

It is recommended to upload sales history for the last 3 years, but at least one year should be uploaded.

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

SDATE

date

NOT NULL

Y

Date of sale/stock

SALES_VOLUME

number(10, 3)

NULL

Y

The amount of units sold in natural units, pcs, kg, liters, and others

SALES_VALUE

number(10, 2)

NULL

Y

SALES_VALUE: The total amount of sales at the checkout minus returns (including VAT). Amounts paid by bonuses and coupons must be excluded. If the total SKU/STORE/DATE amount is negative, fill with 0 (zero)

DISCOUNT_VALUE

number(8, 2)

NULL

 

DISCOUNT_VALUE: The total amount of the discount (including VAT) granted for promotions. E.g. promotions with combined mechanics (for example, 1+1=3, buy 1 and get a discount on the second, and others) must be distributed for all 3 skus. Example: Buy 1 and get 50% discount for second means 100 + 70 * 50% = 135. First SKU discount: 100 - 135100/(100+70) = 20.59, Second SKU discount: 70 - 13570/(100+70) = 14.41

STOCK_VOLUME

number(10, 3)

NULL

Y

Balance at the end of the day in natural units. It is also necessary to fill in for those days where there were no sales (except for days with a zero balance). For new positions, it is filled from the day of the first sale.

STOCK_VALUE

number(12, 2)

NULL

 

Balance amount (for analytics)

LOST_VOLUME

number(10, 3)

NULL

 

Reserved for MySales

TRX_VOLUME

number(10, 3)

NULL

Y

The amount of goods receipts in quantitative terms pcs. kg liters and others

CONSUMED_VOLUME

number(8, 3)

NULL

 

The amount of units used in production (in natural units, kg, liters and others)

WH_VOLUME

number(10, 3)

NULL

 

Wholesale quantity.
Populated by the provided update request

WH_VALUE

number(10, 3)

NULL

 

Wholesale amount.
Populated by the provided update request

STOCK_HOURS

number(3,2)

NULL

 

The share of presence on the balance. To be completed during the second phase of MySales implementation. It is necessary to take into account the time of presence on the balance for culinary and other goods, which always go to zero at the end of the day

MARKDOWN_VOLUME

number(10, 3)

NULL

 

The volume of goods sold due to expiring shelf days period

MARKDOWN_VALUE

number(10, 3)

NULL

 

The amount of goods sold due to expiring shelf days period

SALES_SKU - Aggregated history of sales and balances by week

Data update period: populated by MySales based on the SALES_SKU_DAY table, aggregating them to sales per ISO week.

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

WEEK

int

NOT NULL

Y

ISO week number (201301-201552) in YYYYWW format

SALES_VOLUME

number(15, 3)

NULL

Y

The amount of units sold in natural units, kg, liters and others

SALES_VALUE

number(15, 3)

NULL

Y

The amount of sales at the checkout minus returns UAH. VAT included. Negative values and amounts paid by bonuses are excluded.

DISCOUNT_VALUE

number(15, 3)

NULL

 

The total amount of the granted discount with VAT for various types of promotions

STOCK_VOLUME

number(15, 3)

NULL

Y

Balance at the end of the day in natural units

LOST_VOLUME

number(15, 3)

NULL

 

Reserved by MySales

TRX_VOLUME

number(15, 3)

NULL

Y

Receipts-write-offs in quantitative terms pcs. kg liters and others

CONSUMED_VOLUME

number(8, 3)

NULL

 

The amount of units used in production (in natural units, kg, liters, etc.)

STOCK_DAYS

tinyint

NULL

 

Number of days the item was in stock.

WH_VOLUME

number(8, 1)

NULL

 

Populated by the provided update request

WH_VALUE

number(8, 1)

NULL

 

Populated by the provided update request

SALES_GROUP - History of sales and balances by week, aggregated to product groups

Data update period: populated by MySales based on the SALES_SKU table, aggregating them to product groups by ISO week.

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

GROUP_ID

smallint

NOT NULL

Y

Group identifier from GROUPS.GROUP_ID

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

WEEK

int

NOT NULL

Y

ISO week number (201301-201552) in YYYYWW format

SALES_VOLUME

number(15, 3)

NULL

Y

The quantity of units sold in natural units, kg, liters, etc.

SALES_VALUE

number(15, 3)

NULL

 

The amount of sales at the checkout minus returns. VAT included. Negative values and amounts paid by bonuses are excluded.

DISCOUNT_VALUE

number(15, 3)

NULL

 

The total amount of the granted discount with VAT for various types of promotions

STOCK_VOLUME

number(15, 3)

NULL

Y

Balance at the end of the day in natural units

LOST_VOLUME

number(15, 3)

NULL

 

Reserved by MySales

TRX_VOLUME

number(15, 3)

NULL

Y

Receipts-write-offs in quantitative terms pcs. kg liters etc.

SALES_ITEM_COUNT

smallint

NULL

Y

Number of items sold

STOCK_ITEM_COUNT

smallint

NULL

Y

Number of positions on the stock

CONSUMED_VOLUME

number(8, 3)

NULL

 

The amount of units used in production (in natural units, kg, liters, etc.)

STOCK_DAYS

tinyint

NULL

 

Filled in on the MySales side

WH_VOLUME

number(8, 1)

NULL

 

Populated by the provided update request

WH_VALUE

number(8, 1)

NULL

 

Populated by the provided update request

WH_RECEIPTS - Wholesale receipts

Data update period: daily delete + insert, daily merge

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SDATE

date

NOT NULL

Y

Date of sale

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

RECEIPT_ID

*, varchar(72)

NOT NULL

Y

Check number from the accounting system

VOLUME

number(10, 3)

NULL

Y

Quantity

VALUE

number(10, 3)

NULL

Y

Amount

Replenishment Tables

Master data tables

SUPPLIERS - Suppliers master

Data update period: daily merge without deletion

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SUPPLIER_ID

smallint

NOT NULL

Y

Supplier identifier

SUPPLIER_CODE

nvarchar(9)

NULL

 

Supplier code in the ERP system

SUPPLIER_NAME

nvarchar(256)

NULL

Y

Supplier name

UPDATED

datetime

NULL

 

Update date

CLIENT_CODE

*

*

*

The supplier ID in the client ERP system, if it is not compatible with the smallint type.
Used to transfer data between systems.

CONTRACTS - Contracts master

Data update period: daily merge without deletion

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

CONTRACT_ID

smallint

NOT NULL

Y

Contract identifier

SUPPLIER_ID

smallint

NOT NULL

Y

Supplier identifier from SUPPLIERS.SUPPLIER_ID

CONTRACT_CODE

nvarchar(9)

NULL

 

Contract number in the accounting system

CONTRACT_NAME

nvarchar(256)

NULL

 

Contract name or description

CURRENCY_CODE

nvarchar(5)

NULL

 

Currency code

UPDATED

datetime

NULL

 

Update date

CLIENT_CODE

*

*

*

The contract ID in the client ERP system, if it is not compatible with the smallint type.
Used to transfer data between systems.

SKU_STORE_CONTRACT - Product-store-contract binding

Data update period: daily truncate + insert, daily merge

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

CONTRACT_ID

smallint

NOT NULL

Y

Contract identifier from CONTRACTS.CONTRACT_ID

PACKAGE_CONTENT

numeric(10, 3)

NULL

Y

Multiplicity of packaging

MIN_ORDER_QTY

numeric(10, 3)

NULL

Y

Minimum order, can be equal to 0, 1 or 2-3 multiplicity

PICK_TO_ZERO

tinyint

NULL

 

Pick to zero order flag. Can be maintained in MySales.

PURCHASE_PRICE

numeric(10, 2)

NULL

 

Purchase price without VAT

EXT_MANAGER_ID

smallint

NULL

 

Manager identifier from MANAGER_EXT.ID

UPDATED

datetime

NULL

 

Update date

Presentation stock tables

RPM_PRESENTATION - Presentation stock

Data update period: daily truncate + insert, daily merge

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

PRESENTATION_1

numeric(10, 3)

NOT NULL

 Y

Main presentation stock quantity

PRESENTATION_2

numeric(10, 3)

NULL

 

Additional presentation stock quantity (pre-cash zones, promotions, pallets)

PRESENTATION_3

numeric(10, 3)

NULL

 

Presentation stock quantity 3

PRESENTATION_STOCK_HISTORY - Presentation stock change history

Data update period: daily delete + insert, daily merge, daily insert

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

START_DATE

date

NOT NULL

 Y

Period start date

END_DATE

date

NULL

 

Period end date. If currently active, then the value should be empty (NULL)

PRESENTATION_1

numeric(10, 3)

NOT NULL

 Y

Main presentation stock quantity

PRESENTATION_2

numeric(10, 3)

NULL

 

Additional presentation stock quantity (pre-cash zones, promotions, pallets)

PRESENTATION_3

numeric(10, 3)

NULL

 

Presentation stock quantity 3

MAX_STOCK

numeric(10, 3)

NULL

 

Maximum balance

Order execution tables

RPM_RECEIVINGS - Goods receipts for MySales orders

Data update period: daily insert or (daily delete + insert last N days)

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

UPLOAD_DATE

datetime

NOT NULL

Y

Receipt order upload date

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

ORDER_NUMBER

int

NOT NULL

Y

MySales order identifier

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

ORDER_DATE

date

NOT NULL

Y

MySales order date

EXPECTED_DATE

date

NULL

Y

Expected Date of arrival according to the delivery schedule

RECEIPT_DATE

datetime

NULL

Y

Goods receipt date

QUANTITY

numeric(12, 3)

NOT NULL

Y

Received quantity

RPM_ORDER_ADJUSTMENTS - Order adjustments

Data update period: daily delete + insert, daily merge, daily insert

Optional data to upload. Only in the case of using adjustments from the accounting system

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

ORDER_NUMBER

int

NOT NULL

Y

MySales order identifier

ADJUSTED_QUANTITY

numeric(11,2)

NOT NULL

Y

Adjusted Order Quantity

RPM_ORDER_DELAY - Dates until which orders are delayed

Data update period: daily delete + insert, daily merge, daily insert

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

ORDER_NUMBER

int

NOT NULL

Y

Order number received from MySales

ADJUSTED_DELIVERY

date

NOT NULL

Y

Date until which the order is delayed

RPM_ORDER_CLOSE - Order closing documents

Data update period: daily delete + insert, daily merge, daily insert

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

ORDER_NUMBER

int

NOT NULL

Y

MySales order identifier

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

CLOSE_DATE

date

NOT NULL

Y

Order closing date

External (Manual) orders

External (Manual) orders are used to correctly calculate in transit goods receipts in MySales orders.

MANUAL_ORDERS - External (manual) orders

Data update period: daily delete + insert, daily merge, daily insert

This table filled by documents that are created in ERP system (e.g. not in MySales system)

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

ORDER_NUMBER

*, int

NOT NULL

Y

Order number from ERP system

ORDER_DATE

date

NOT NULL

Y

Order date from ERP system

DELIVERY_DATE

date

NOT NULL

Y

Scheduled delivery date

ORDER_VOLUME

number(11,2)

NOT NULL

Y

Quantity

MANUAL_ORDER_RECEIVINGS - Good receipts for external (manual) orders

Data update period: daily delete + insert, daily merge, daily insert

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

ORDER_NUMBER

*, int

NOT NULL

Y

Order number from ERP system

RECEIPT_DATE

date

NOT NULL

Y

Goods receipt date

QUANTITY

number(11,2)

NOT NULL

Y

Quantity received

MANUAL_ORDER_CLOSE - Order closing documents for external (manual) orders

Data update period: daily delete + insert, daily merge, daily insert

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

ORDER_NUMBER

*, int

NOT NULL

Y

Order number from ERP system

CLOSE_DATE

datetime

NOT NULL

Y

Date of order closing document

Other tables

RPM_TRANSFERS - Goods transfers between warehouses

Data update period: *

It is used only if it is necessary to take into account movements between warehouses to close receipts to the warehouse from which distribution to stores takes place.

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

FROM_STORE_ID

smallint

NOT NULL

Y

Warehouse identifier from STORES.STORE_ID from which the transfer is made

TO_STORE_ID

smallint

NOT NULL

Y

Warehouse identifier from STORES.STORE_ID to which the transfer is taking place

TRANS_NUMBER

varchar(50)

NOT NULL

Y

Transfer number from the ERP system

RECEIPT_DATE

date

NOT NULL

Y

Date of move

QUANTITY

numeric(12,3)

NOT NULL

Y

Quantity

LATEST_STOCK - Current stock balances table for stores (warehouses)

Data update period: daily truncate + insert

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

QUANTITY

numeric(15, 3)

NOT NULL

Y

Quantity on the balance (you can subtract the amount of the discounted goods, blocked goods and so on)

SDATE

date

NOT NULL

Y 

Date of stock balances

EXT_MANAGER - External managers

Data update period: daily merge, truncate + insert

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

ID

smallint

NOT NULL

Y

Manager identifier

NAME

nvarchar(120)

NOT NULL

Y

Manager name

EMAIL

nvarchar(100)

NULL

 

e-mail

PHONE

nvarchar(50)

NULL

 

Phone number

UPDATED

datetime

NULL

 

Update date

CLIENT_CODE

*

*

*

The manager ID in the client ERP system, if it is not compatible with the smallint type.
Used to transfer data between systems.

SKU_STORE_MANAGER - Responsible product manager

Data update period: daily truncate + insert, daily merge

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

STORE_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

EXT_MANAGER_ID

smallint

NOT NULL

Y

Manager number from EXT_MANAGER.ID

SKU_STORE_DISABLED_ORDERS - Store order blocking periods

Data update period: *

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

START_DATE

date

NOT NULL

Y

Period start date

END_DATE

date

NULL

 

Period end date

SKU_WHS_DISABLED_ORDERS - Warehouse order blocking periods

Data update period: *

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

START_DATE

date

NOT NULL

Y

Period start date

END_DATE

date

NULL

 

Period end date

Reorder point tables

These tables are only used for the reorder point functionality.

UOM_CLASSES - UOM (Unit Of Measure) classes

Data update period: *

Used for reorder point logistics optimization

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

UOM

nvarchar(10)

NOT NULL

Y

To order your own imports and optimize logistics

UOM_TYPE

nvarchar(10)

NOT NULL

Y

Unit type: BASE - Base BASE_X - Base derivative BASE_A - Base, alternative COM - Commercial (no conversion units)

UOM_CLASS

nvarchar(10)

NOT NULL

Y

Unit class for base (BASE) PIECE, WEIGHT, VOLUME, LENGTH, SQ

PRECISION 

smallint

NOT NULL

Y

Accuracy, number of characters

X

numeric(10,5)

NOT NULL

Y

Calculation coefficient (x = 12 means 1 box = 12 pcs)

UOM_X

nvarchar(10)

NOT NULL

Y

Link to recalculation (UOM)

UOM_X_BASE

nvarchar(10)

NOT NULL

Y

Recalculation to base unit of measure

UOM_NAME

nvarchar(15)

NOT NULL

Y

Name of the unit of measurement

UPDATED

datetime

NULL

 

Date of change

CLIENT_CODE

*

*

*

The UOM ID in the client ERP system, if it is not compatible with the nvarchar(*) type.
Used to transfer data between systems.

PRODUCTS_UOM - Product units conversion

Data update period: *

Used for reorder point logistics optimization

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

SKU_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

UOM

nvarchar(10)

NOT NULL

Y

Unit Reference (Table UOM_CLASSES.UOM)

UOM_NAME

nvarchar(10)

NOT NULL

Y

Name - for example, for piece goods

X

numeric(10,5)

NOT NULL

Y

Conversion factor for derived units = X/Y

Y

numeric(10,5)

NOT NULL

Y

Conversion factor for derived units = X/Y

UOM_X

nvarchar(10)

NOT NULL

Y

Reference to the unit of conversion

UOM_X_BASE

numeric(10,3)

NOT NULL

Y

Quantity from the base unit of measurement - calculated value

NET_WIGHT

numeric(10,3)

NULL

 

Net weight - filled in for the base unit of measure only

NET_VOLUME

numeric(10,3)

NULL

 

Net volume - filled in for the base unit of measure only

GROSS_WEIGHT

numeric(10,3)

NULL

 

Gross weight - logistics - filled in for all units of measure

GROSS_VOLUME

numeric(10,3)

NULL

 

Gross volume - logistics - filled in for all units of measure

LENGTH

numeric(8,3)

NULL

 

Length (overall length - logistics)

WIDTH

numeric(8,3)

NULL

 

Width (overall width - logistics)

HEIGHT

numeric(8,3)

NULL

 

Height (Overall height - logistics)

UPDATED_BY

int

NULL

 

The user who made the changes

UPDATED

datetime

NULL

 

Date of change

CLIENT_CODE

*

*

*

Used to transfer data between systems.

Master forecast tables

MASTER_FCST - Master forecast data table at the SKU level

Column name

Data type

Column Description

Note

Column name

Data type

Column Description

Note

SKU_ID

int

Product identifier from PRODUCTS.SKU_ID

 

WEEK

int

ISO week (YYYYWW)

We have a function dbo.iso_week which turns a date into ISO WEEK format

UPDATED_BY

int

The code of the user that the master changed

You can use one system number when loading from 1C, for example -2

CREATED

datetime

date of creation

Populated as getdate() when the object is first created

ALTERED

datetime

Date of change

Populated as getdate() whenever the object changes

DELETED

datetime

Deletion date

Populated as getdate() if the master needs to be removed. For active master - NULL

NOTES

nvarchar(256)

Notes

 

REVISION

int

Version number

The master of version 0 is always active in the system. When changing the master, the old entry is not deleted, it is assigned the version max(revision)+1, and the new version is inserted with revision = 0

TYPE

tinyint

Master type: 1-promo, 2-new, 3-other

 

DATE_F

date

Wizard start date

Information field only, does not take part in the calculation

DATE_T

date

Master end date

Information field only, does not take part in the calculation

ALL_STORES

tinyint

Sign "All stores". If the master is set up for all stores - 1, if for some - 0

 

FIX

tinyint

Sign "Fix master". 1 - fix, 0 - do not fix

If the master is fixed, it will be distributed regardless of sales in the specified quantity. If the master is not fixed, the daily adjustment will be able to lower and raise it depending on sales for the period of the master

MASTER_FCST_STORES _ List of Stores for master forecast

This is a table of data about the stores for which the master is started (It is started only for those forecast masters from the MASTER_FCST table, where ALL_STORES = 0)

Column name

Data type

Column Description

Note

Column name

Data type

Column Description

Note

SKU_ID

int

Product identifier from PRODUCTS.SKU_ID

Corresponds to the SKU_ID field from the MASTER_FCST table

WEEK

int

ISO week (YYYYWW)

Corresponds to the WEEK field from the MASTER_FCST table

REVISION

int

Version number

Corresponds to the REVISION field from the MASTER_FCST table

STORE_ID

int

Store code MySales

 

DELETED

datetime

Deletion date

Corresponds to the DELETED field from the MASTER_FCST table

Promotion tables

PROMO_HEADER - Promo header

Column name

Data type

Column Description

Note

Column name

Data type

Column Description

Note

ID

int

Promo identifier / number

Selected as the maximum promo number from the table + 1

START_DATE

date

Promo start date

 

END_DATE

date

Promo end date

 

TYPE

char(1)

Promo type: M - MMK, T - TPR

 

SRC_ID

 

 

 

BENEFIT_PERC

numeric(5,2)

Discount value at the level of the entire promo (30, 40, ...)

If the discount is set at the SKU level, we load the average here

NAME

nvarchar(100)

Promo name

 

MECHANIC

tinyint

Promo mechanics. For MMK - number of mechanics, for TPR - NULL

A list of possible mechanic numbers with a description will be provided

COMPENSATION

 

 

 

APPROVED_BY

smallint

Number of the user who confirmed the promo. Unconfirmed promo - NULL

Needs further discussion - where will the promo confirmation/change be managed

UPDATED_DATE

date

Creation / modification date

 

UPDATED_BY

smallint

The number of the user who modified the promo. Populated every time the object changes

 

CREATED_BY

smallint

Number of the user who created the promo. Filled in when the object is created

 

SALES_LY

 

 

 

BASE_CY

 

 

 

PROMO_CY

 

 

 

FORMATED_STORES_ONLY

 

 

 

CREATED

date

The date the object was created. Filled in as cast(getdate() as date) when the object is created

 

COMPENSATION_TYPE

 

 

 

COMPENSATION_LIMIT

 

 

 

MANUAL_UPLIFT

 

 

 

COMMUNICATIONS

nvarchar(20)

List of communications at the level of everything separated by commas. If there is no communication - NULL

List of possible communication numbers with description will be provided

SALES_CY

 

 

 

RECALCULATED

 

 

 

REMARKS

nvarchar(256)

Notes

 

PROMO_SKU - Promo Products list

Column name

Data type

Column Description

Column name

Data type

Column Description

ID

int

Promo identifier from PROMO_HEADAR.ID

SKU_ID

int

Product identifier from PRODUCTS.SKU_ID

BENEFIT_PERC

decimal(5,2)

Discount value at the SKU level (30, 40, ...). If not applicable - NULL

CREATED

date

The date the object was created. Equal to the CREATED field in the PROMO_HEADER table

UPLIFT_BEFORE_DISC

 

 

UPLIFT_AFTER_DISC

 

 

SALES_PRICE

decimal(8,2)

Filled automatically from the PRICES table. Loaded if it is necessary to use a price different from the PRICES table

PURCHASE_PRICE

 

 

MANUAL_UPLIFT

 

 

COMMUNICATIONS

varchar(64)

Comma-separated list of communications at the SKU level. If there is no communication - NULL

PROMO_STORES - Promo Stores list

Column name

Data type

Column Description

Column name

Data type

Column Description

ID

int

Promo identifier from PROMO_HEADAR.ID

STORE_ID

smallint

Store identifier from STORES.STORE_ID

PROMO_REVISION - table with promo versions (Copies PROMO_HEADER table)

Column name

Data type

Column Description

Column name

Data type

Column Description

ID

int

Promo identifier from PROMO_HEADAR.ID

REV

tinyint

Always value 0 when downloading promo from ERP

BENEFIT_PERC

numeric(5,2)

Corresponds to BENEFIT_PERC in PROMO_HEADER table

NAME

nvarchar(100)

Corresponds to NAME in PROMO_HEADER table

MECHANIC

tinyint

Corresponds to MECHANIC in PROMO_HEADER table

COMPENSATION

 

 

APPROVED_BY

smallint

Corresponds to APPROVED_BY in PROMO_HEADER table

UPDATED_DATE

date

Corresponds to UPDATED_DATE in PROMO_HEADER table

UPDATED_BY

smallint

Corresponds to UPDATED_BY in PROMO_HEADER table

CREATED_BY

smallint

Corresponds to CREATED_BY in PROMO_HEADER table

TYPE

char(1)

Corresponds to TYPE in PROMO_HEADER table

FORMATED_STORES_ONLY

 

 

COMPENSATION_TYPE

 

 

COMPENSATION_LIMIT

 

 

MANUAL_UPLIFT

 

 

COMMUNICATIONS

varchar(20)

Corresponds to COMMUNICATIONS in the PROMO_HEADER table

REMARKS

nvarchar(256)

Corresponds to REMARKS in PROMO_HEADER table

Data completion flag

SEMAPHORE - Data loading completion flag

Update period: At the end of successful data upload

Column name

Data type

Allow NULL

Required field

Column Description

Column name

Data type

Allow NULL

Required field

Column Description

COMPLETED_DATE

datetime

NOT NULL

Y

Timestamp of the end of uploading data from the customer's storage to MySales

Loading Orders from MySales

RPM_ORDER_EXPORT_V - view table for uploading orders from MySales to ERP system

Update period: At the end of orders calculation, after confirming order, Usually every five minutes.

Column Name

Data Type

Description

Column Name

Data Type

Description

ID

int

Order identifier / number created by MySales

STORE_ID

smallint

Store identifier from STORES.STORE_ID

STORE_CLIENT_CODE

*

STORES.CLIENT_CODE

SUPPLIER_ID

smallint

Supplier identifier from SUPPLIERS.SUPPLIER_ID

SUPPLIER_CLIENT_CODE

*

SUPPLIERS.CLIENT_CODE

CONTRACT_ID

smallint

Contract identifier from CONTRACTS.CONTRACT_ID

CONTRACT_CLIENT_CODE

*

CONTRACTS.CLIENT_CODE

WHS_ID

smallint

Warehouse identifier from STORES.STORE_ID

WHS_CLIENT_CODE

*

STORES.CLIENT_CODE

WHS_ORDER_ID

int

Оnly for cross-dock store orders.

Warehouse order identifier / number for cross-dock combined order

PUSH_ID

int

Оnly for push orders.

Push identifier / number RPM_PUSH.ID

ADD_LOAD_ID

int

Оnly if the order with the additional load period.

Additional load identifier / number from RPM_ADD.ID

ORDER_DATE

date

Order Date

DELIVERY_DATE

date

Planned Delivery Date

SEPARATE_ORDER_TYPE

varchar(5)

Sign of a split order: pz - pick to zero, pr - promo, pzpr - pick to zero promo, t - tender, pzt - pick to zero tender, prt - promo tender

STORE_CROSS_DOCK_ORDERS

varchar

Order identifiers / numbers of stores cross-dock warehouse order

SKU_ID

int

Product identifier from PRODUCTS.SKU_ID

PRODUCT_CLIENT_CODE

*

PRODUCTS.CLIENT_CODE

EXT_MANAGER_ID

smallint

Manager identifier for the product / store from
EXT_MANAGER.ID

EXT_MANAGER_CLIENT_CODE

 

EXT_MANAGER.CLIENT_CODE

ORDER_VOLUME

number(11,2)

Order Quantity

UPDATE_DATE

datetime

Last modified order date