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 |
PRICE_TYPE | varchar(255) | 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 |
|---|---|---|---|---|
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.
If the ERP system does not maintain the history of the assortment matrix, then SKU_RANGE must be filled in according to the provided update request.
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.
Required if SKU_RANGE is not filled in
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.
Filled in three years to form tables SALES_SKU, SALES_SKU_SHARE, SALES_GROUP, SALES_GROUP_SHARE.
In the future, it is carried out only for a year.
You need to run after_SALES_SKU_DAY @sate procedure script after data upload, where @sdate is the first data modification date. This procedure recalculates SALES_SKU and SALES_GROUP tables.
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. |
WH_VALUE | number(10, 3) | NULL |
| Wholesale amount. |
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.
Populated based on the SALES_SKU_DAY provided by the after_SALES_SKU_DAY procedure script.
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. |
RECEIPT_COUNT | smallint | NULL |
| Number of cash receipts the item |
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.
Populated based on the SALES_SKU_DAY provided by the after_SALES_SKU_DAY procedure script.
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
This table is filled only if there are wholesale sales exists
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 |
|---|---|---|---|---|
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. |
CONTRACTS - Contracts master
Data update period: daily merge without deletion
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. |
SKU_STORE_CONTRACT - Product-store-contract binding
Data update period: daily truncate + insert, daily merge
You should use a virtual supplier contract and supplier for Warehouse-to-Store orders
Only one supplier contract can be used for purchase order chain.
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 |
|---|---|---|---|---|
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
Updated by procedure script after uploading presentation stock
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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
Usually filled by after_SALES_SKU_DAY procedure.
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 |
|---|---|---|---|---|
ID | smallint | NOT NULL | Y | Manager identifier |
NAME | nvarchar(120) | NOT NULL | Y | Manager name |
nvarchar(100) | NULL |
| ||
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. |
SKU_STORE_MANAGER - Responsible product manager
Data update period: daily truncate + insert, daily merge
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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. |
PRODUCTS_UOM - Product units conversion
Data update period: *
Used for reorder point logistics optimization
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 |
|---|---|---|---|
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 |
|---|---|---|---|
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 |
|---|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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 |
|---|---|---|
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
You can execute after_ALL_INTERFACES stored procedure at the end of successful data upload
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.
You should store correspondence of MySales Order ID and ERP System Order ID for backward interfaces of Order execution tables. You can create additional custom table in ERP database or add addition custom field with MySales_Order_ID into ERP Order header table.
Use ORDER_DATE to filter new current and future orders. We have prohibited the creation of orders for past dates.
After loading current and future orders, compare them with previously imported orders and import only new orders that are absent in the ERP system.
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_CLIENT_CODE |
| EXT_MANAGER.CLIENT_CODE |
ORDER_VOLUME | number(11,2) | Order Quantity |
UPDATE_DATE | datetime | Last modified order date |