Specification of Integration
- Sergey Schiller (Unlicensed)
- Alexey Ivasyuk
- Pawel Wos
- Dmitry Dyachenko (Unlicensed)
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.
- 1 Overview
- 1.1 Data types
- 1.2 ETL process example
- 1.2.1 Master data
- 1.2.2 General data
- 1.2.3 Sales and stock data
- 2 Forecast Tables
- 2.1 Master data
- 2.2 General data
- 2.3 Assortment matrices
- 2.4 Sales and stock data
- 3 Replenishment Tables
- 3.1 Master data tables
- 3.2 Presentation stock tables
- 3.3 Order execution tables
- 3.4 External (Manual) orders
- 3.5 Other tables
- 3.5.1 RPM_TRANSFERS - Goods transfers between warehouses
- 3.5.2 LATEST_STOCK - Current stock balances table for stores (warehouses)
- 3.5.3 EXT_MANAGER - External managers
- 3.5.4 SKU_STORE_MANAGER - Responsible product manager
- 3.5.5 SKU_STORE_DISABLED_ORDERS - Store order blocking periods
- 3.5.6 SKU_WHS_DISABLED_ORDERS - Warehouse order blocking periods
- 4 Reorder point tables
- 5 Master forecast tables
- 6 Promotion tables
- 7 Data completion flag
- 8 Loading Orders from MySales
Overview
The MySales system works with a wide range of databases (Oracle, Microsoft SQL Server, MySQL, PostgreeSQL, Oracle Teradata and etc.).
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.
You can use the following options to create a database:
Database instance on separate server
Separate database instance on the same server as the ERP database instance.
Separate database on ERP server database instance (recommended).
Separate database schema on same database on ERP server database instance.
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 |
|---|---|---|---|---|
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) | smallint | smallint |
identity insert | IDENTITY | SEQUENCE | AUTO_INCREMENT | serial (integer) |
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:
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.
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.
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:
truncate table msls.SKU_RANGE_CURRENT;
insert into msls.SKU_RANGE_CURRENT with (tablock) (STORE_ID, SKU_ID)
select S.STORE_ID, P.SKU_ID
from DbLink.ERPDB.dbo.ASSORTMENT_MATRIX as AX with (nolock)
inner join msls.STORES as S with (nolock) on S.CLIENT_CODE = AX.STORE_IDENT
inner join msls.PRODUCTS as P with (nolock) on P.CLIENT_CODE = AX.PRODUCT_ID;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.
declare @end_date as date = DATEADD(DAY, -1, cast(getdate() as date));
declare @start_date as date = DATEADD(DAY, -3, @end_date);
delete from msls.SALES_SKU_DATE where sdate > @start_date;
insert into msls.SALES_SKU_DAY with (tablock) (SDATE, STORE_ID, SKU_ID,
SALES_VOLUME, SALES_VALUE, DISCOUNT_VALUE, STOCK_VOLUME, TRX_VOLUME)
select SDATE, STORE_ID, SKU_ID,
sum(SALES_VOLUME) as SALES_VOLUME,
sum(SALES_VALUE) as SALES_VALUE,
sum(DISCOUNT) as DISCOUNT,
sum(STOCK_VOLUME) as STOCK_VOLUME,
sum(TRX_VOLUME) as TRX_VOLUME
FROM (
select SALES_DATE as SDATE, S.STORE_ID, P.SKU_ID,
iif(SALES_QUANTITY - RETURN_QUANTITY > 0, SALES_QUANTITY - RETURN_QUANTITY, 0) as SALES_VOLUME,
iif(TOTAL_AMOUNT > 0, TOTAL_AMOUNT, 0) as SALES_VALUE,
DISCOUNT as DISCOUNT_VALUE,
cast(0 as number(10,3)) as STOCK_VOLUME,
cast(0 as number(10,3)) as TRX_VOLUME
from DbLink.ERPDB.dbo.DAILY_SALES as D with (nolock)
inner join msls.STORES as S with (nolock) on S.CLIENT_CODE = D.STORE_IDENT
inner join msls.PRODUCTS as P with (nolock) on P.CLIENT_CODE = D.PRODUCT_ID
where SALES_DATE between @start_date and @end_date
union all
select BALANCE_DATE as SDATE, S.STORE_ID, P.SKU_ID,
cast(0 as number(10,3)) as SALES_VOLUME,
cast(0 as number(10,2)) as SALES_VALUE,
cast(0 as number(8,2)) as DISCOUNT_VALUE,
QUANTITY as STOCK_VOLUME,
cast(0 as number(10,3)) as TRX_VOLUME
from DbLink.ERPDB.dbo.INVENTORY_BALANCE as I with (nolock)
inner join msls.STORES as S with (nolock) on S.CLIENT_CODE = I.STORE_IDENT
inner join msls.PRODUCTS as P with (nolock) on P.CLIENT_CODE = I.PRODUCT_ID
where BALANCE_DATE between @start_date and @end_date
union all
select RECEIPT_DATE as SDATE, S.STORE_ID, P.SKU_ID,
cast(0 as number(10,3)) as SALES_VOLUME,
cast(0 as number(10,2)) as SALES_VALUE,
cast(0 as number(8,2)) as DISCOUNT_VALUE,
cast(0 as number(10,3)) as STOCK_VOLUME,
sum(QUANTITY) as TRX_VOLUME
from DbLink.ERPDB.dbo.INVENTORY_GOODS_RECEIPTS as R with (nolock)
inner join msls.STORES as S with (nolock) on S.CLIENT_CODE = R.STORE_IDENT
inner join msls.PRODUCTS as P with (nolock) on P.CLIENT_CODE = R.PRODUCT_ID
where RECEIPT_DATE between @start_date and @end_date
group by RECEIPT_DATE, S.STORE_ID, P.SKU_ID
) as vt
group by SDATE, STORE_ID, SKU_ID;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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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. |
General data
PRICES - Retail prices
Data update period: daily
Prices are also filled in for future periods if they are known.
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 / |
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. |
If the client's system does not maintain prices by date, it is possible to fill in the simplified PRICES_CURRENT table, on the basis of which the PRICES table will be automatically generated.
PRICES_CURRENT - Current retail prices
Data update period: daily truncate + insert, daily merge
This table is only populated if the customer prefers it as a simpler alternative to the PRICES table. Once the PRICES_CURRENT table is populated, a query is run that automatically generates the PRICES table.
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 | int | NOT_NULL | 1 | Store identifier from STORES.STORE_ID / |
PRICE | number(10, 2) | NULL | 1 | Price with Value Added Tax |