Specification of Integration
- 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: