Versions Compared

Old Version 33

changes.mady.by.user Alexey Ivasyuk

Saved on

compared with

New Version Current

changes.mady.by.user Dmitry Dyachenko

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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.

...

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)

...

Code Block
languagesql
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;

...

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.

...

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.

...

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.

...

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.

...

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.

...

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.

...

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.

...

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.

...

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.

...

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.

...

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