int

number(10)

int

integer

smallint

number(5)

smallint

smallint

tinyint

number(3)
number(1)

smallint

smallint

IDENTITY

SEQUENCE

AUTO_INCREMENT

serial (integer)
smallserial (smallint)

char(*)

char(*)

char(*) character set latin1

char(*)

nchar

nchar(*)

char(*) character set utf8mb4

char(*)

varchar(*)

varchar2(*)

varchar(*) character set latin1

varchar(*)

nvarchar(*)

nvarchar2(*)

varchar(*) character set utf8mb4

varchar(*)

date

date

date

date

datetime

timestamp

datetime

timestamp without timezone

numeric(p,s)

number(p,s)

numeric(p,s)

numeric(p,s)

smallint

NOT NULL

Y

Price number

nvarchar(256)

NULL

Name

datetime

NULL

Update date

*

*

*

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

Used to transfer data between systems.

smallint

NOT NULL

Y

Region number

nvarchar(256)

NULL

Y

Region name

*

*

*

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

Used to transfer data between systems.

smallint

NOT NULL

Y

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

nvarchar(256)

NOT NULL

Y

Store name

smallint

NOT NULL

Y

Region number from the REGIONS table

smallint

NULL

Price serial number from the PRICELINES table

date

NULL

Store opening date

date

NULL

Store closing date

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.

nvarchar(256)

NULL

Store Address

datetime

NULL

Update date

tinyint

NULL

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

tinyint

NULL

Store Format Id

char(1)

NULL

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

*

*

*

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.

smallint

NOT NULL

Y

Group number

nvarchar(256)

NULL

Y

The name of the group of goods. Unique.

nvarchar(32)

NULL

Group code from accounting system

smallint

NULL

Paternal group number (for building hierarchies)

number(12, 2)

NULL

Filled in on the MySales side

number(12, 2)

NULL

Filled in on the MySales side

number(8, 2)

NULL

Filled in on the MySales side

number(10, 2)

NULL

Filled in on the MySales side

datetime

NULL

Update date

*

*

*

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

Used to transfer data between systems.

int

NOT NULL

Y

Product identifier

smallint

NOT NULL

Y

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

nvarchar(256)

NOT NULL

Y

Name of product. Unique

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

smallint

NULL

Supplier identifier from SUPPLIERS table.
()

number(10, 2)

NULL

Purchase price WITHOUT value added tax

number(10, 2)

NULL

Filled in on the MySales side

number(10, 2)

NULL

Filled in on the MySales side

number(10, 2)

NULL

Filled in on the MySales side

number(10, 2)

NULL

Filled in on the MySales side

smallint

NULL

Required for Replenishment

Total expiration period, in days.

(Expiration date minus production date)

number(10, 3)

NULL

Only for Reorder Point

Multiplicity of orders for DC

number(10, 3)

NULL

Only for Reorder Point

Minimum order quantity

nvarchar(10)

NULL

unit of measurement

datetime

NULL

insert or update date

number(5, 2)

NULL

Only for Price Optimisation

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

number(10, 3)

NULL

Only for Replenishment

Product weight, kg

number(10, 3)

NULL

Product volume, l

number(6, 2)

NULL

Product height, см

number(6, 2)

NULL

Product width, cm

number(6, 2)

NULL

Product length, cm

*

*

*

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

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

varchar(20)

NOT NULL

Y

Product barcode

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

date

NOT NULL

Y

Date of sale/stock

number(10, 3)

NULL

Y

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

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)

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

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.

number(12, 2)

NULL

Balance amount (for analytics)

number(10, 3)

NULL

Reserved for MySales

number(10, 3)

NULL

Y

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

number(8, 3)

NULL

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

number(10, 3)

NULL

Wholesale quantity.
Populated by the provided update request

number(10, 3)

NULL

Wholesale amount.
Populated by the provided update request

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

number(10, 3)

NULL

The volume of goods sold due to expiring shelf days period

number(10, 3)

NULL

The amount of goods sold due to expiring shelf days period

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

int

NOT NULL

Y

ISO week number (201301-201552) in YYYYWW format

number(15, 3)

NULL

Y

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

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.

number(15, 3)

NULL

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

number(15, 3)

NULL

Y

Balance at the end of the day in natural units

number(15, 3)

NULL

Reserved by MySales

number(15, 3)

NULL

Y

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

number(8, 3)

NULL

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

tinyint

NULL

Number of days the item was in stock.

number(8, 1)

NULL

Populated by the provided update request

number(8, 1)

NULL

Populated by the provided update request

smallint

NOT NULL

Y

Group identifier from GROUPS.GROUP_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

int

NOT NULL

Y

ISO week number (201301-201552) in YYYYWW format

number(15, 3)

NULL

Y

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

number(15, 3)

NULL

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

number(15, 3)

NULL

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

number(15, 3)

NULL

Y

Balance at the end of the day in natural units

number(15, 3)

NULL

Reserved by MySales

number(15, 3)

NULL

Y

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

smallint

NULL

Y

Number of items sold

smallint

NULL

Y

Number of positions on the stock

number(8, 3)

NULL

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

tinyint

NULL

Filled in on the MySales side

number(8, 1)

NULL

Populated by the provided update request

number(8, 1)

NULL

Populated by the provided update request

date

NOT NULL

Y

Date of sale

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

*, varchar(72)

NOT NULL

Y

Check number from the accounting system

number(10, 3)

NULL

Y

Quantity

number(10, 3)

NULL

Y

Amount

smallint

NOT NULL

Y

Supplier identifier

nvarchar(9)

NULL

Supplier code in the ERP system

nvarchar(256)

NULL

Y

Supplier name

datetime

NULL

Update date

*

*

*

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

smallint

NOT NULL

Y

Contract identifier

smallint

NOT NULL

Y

Supplier identifier from SUPPLIERS.SUPPLIER_ID

nvarchar(9)

NULL

Contract number in the accounting system

nvarchar(256)

NULL

Contract name or description

nvarchar(5)

NULL

Currency code

datetime

NULL

Update date

*

*

*

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

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

smallint

NOT NULL

Y

Contract identifier from CONTRACTS.CONTRACT_ID

numeric(10, 3)

NULL

Y

Multiplicity of packaging

numeric(10, 3)

NULL

Y

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

tinyint

NULL

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

numeric(10, 2)

NULL

Purchase price without VAT

smallint

NULL

Manager identifier from MANAGER_EXT.ID

datetime

NULL

Update date

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

numeric(10, 3)

NOT NULL

 Y

Main presentation stock quantity

numeric(10, 3)

NULL

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

numeric(10, 3)

NULL

Presentation stock quantity 3

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

date

NOT NULL

 Y

Period start date

date

NULL

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

numeric(10, 3)

NOT NULL

 Y

Main presentation stock quantity

numeric(10, 3)

NULL

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

numeric(10, 3)

NULL

Presentation stock quantity 3

numeric(10, 3)

NULL

Maximum balance

datetime

NOT NULL

Y

Receipt order upload date

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

int

NOT NULL

Y

MySales order identifier

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

date

NOT NULL

Y

MySales order date

date

NULL

Y

Expected Date of arrival according to the delivery schedule

datetime

NULL

Y

Goods receipt date

numeric(12, 3)

NOT NULL

Y

Received quantity

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

int

NOT NULL

Y

MySales order identifier

numeric(11,2)

NOT NULL

Y

Adjusted Order Quantity

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

int

NOT NULL

Y

Order number received from MySales

date

NOT NULL

Y

Date until which the order is delayed

int

NOT NULL

Y

MySales order identifier

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

date

NOT NULL

Y

Order closing date

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

*, int

NOT NULL

Y

Order number from ERP system

date

NOT NULL

Y

Order date from ERP system

date

NOT NULL

Y

Scheduled delivery date

number(11,2)

NOT NULL

Y

Quantity

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

*, int

NOT NULL

Y

Order number from ERP system

date

NOT NULL

Y

Goods receipt date

number(11,2)

NOT NULL

Y

Quantity received

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

*, int

NOT NULL

Y

Order number from ERP system

datetime

NOT NULL

Y

Date of order closing document

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

smallint

NOT NULL

Y

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

smallint

NOT NULL

Y

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

varchar(50)

NOT NULL

Y

Transfer number from the ERP system

date

NOT NULL

Y

Date of move

numeric(12,3)

NOT NULL

Y

Quantity

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

numeric(15, 3)

NOT NULL

Y

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

date

NOT NULL

Y 

Date of stock balances

smallint

NOT NULL

Y

Manager identifier

nvarchar(120)

NOT NULL

Y

Manager name

nvarchar(100)

NULL

e-mail

nvarchar(50)

NULL

Phone number

datetime

NULL

Update date

*

*

*

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

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

smallint

NOT NULL

Y

Store identifier from STORES.STORE_ID

smallint

NOT NULL

Y

Manager number from EXT_MANAGER.ID

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

date

NOT NULL

Y

Period start date

date

NULL

Period end date

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

date

NOT NULL

Y

Period start date

date

NULL

Period end date

nvarchar(10)

NOT NULL

Y

To order your own imports and optimize logistics

nvarchar(10)

NOT NULL

Y

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

nvarchar(10)

NOT NULL

Y

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

smallint

NOT NULL

Y

Accuracy, number of characters

numeric(10,5)

NOT NULL

Y

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

nvarchar(10)

NOT NULL

Y

Link to recalculation (UOM)

nvarchar(10)

NOT NULL

Y

Recalculation to base unit of measure

nvarchar(15)

NOT NULL

Y

Name of the unit of measurement

datetime

NULL

Date of change

*

*

*

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

int

NOT NULL

Y

Product identifier from PRODUCTS.SKU_ID

nvarchar(10)

NOT NULL

Y

Unit Reference (Table UOM_CLASSES.UOM)

nvarchar(10)

NOT NULL

Y

Name - for example, for piece goods

numeric(10,5)

NOT NULL

Y

Conversion factor for derived units = X/Y

numeric(10,5)

NOT NULL

Y

Conversion factor for derived units = X/Y

nvarchar(10)

NOT NULL

Y

Reference to the unit of conversion

numeric(10,3)

NOT NULL

Y

Quantity from the base unit of measurement - calculated value

numeric(10,3)

NULL

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

numeric(10,3)

NULL

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

numeric(10,3)

NULL

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

numeric(10,3)

NULL

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

numeric(8,3)

NULL

Length (overall length - logistics)

numeric(8,3)

NULL

Width (overall width - logistics)

numeric(8,3)

NULL

Height (Overall height - logistics)

int

NULL

The user who made the changes

datetime

NULL

Date of change

*

*

*

Used to transfer data between systems.

int

Product identifier from PRODUCTS.SKU_ID

int

ISO week (YYYYWW)

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

int

The code of the user that the master changed

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

datetime

date of creation

Populated as getdate() when the object is first created

datetime

Date of change

Populated as getdate() whenever the object changes

datetime

Deletion date

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

nvarchar(256)

Notes

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

tinyint

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

date

Wizard start date

Information field only, does not take part in the calculation

date

Master end date

Information field only, does not take part in the calculation

tinyint

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

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

int

Product identifier from PRODUCTS.SKU_ID

Corresponds to the SKU_ID field from the MASTER_FCST table

int

ISO week (YYYYWW)

Corresponds to the WEEK field from the MASTER_FCST table

int

Version number

Corresponds to the REVISION field from the MASTER_FCST table

int

Store code MySales

datetime

Deletion date

Corresponds to the DELETED field from the MASTER_FCST table

int

Promo identifier / number

Selected as the maximum promo number from the table + 1

date

Promo start date

date

Promo end date

char(1)

Promo type: M - MMK, T - TPR

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

nvarchar(100)

Promo name

tinyint

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

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

smallint

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

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

date

Creation / modification date

smallint

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

smallint

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

date

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

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

nvarchar(256)

Notes

int

Promo identifier from PROMO_HEADAR.ID

int

Product identifier from PRODUCTS.SKU_ID

decimal(5,2)

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

date

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

decimal(8,2)

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

varchar(64)

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

int

Promo identifier from PROMO_HEADAR.ID

smallint

Store identifier from STORES.STORE_ID

int

Promo identifier from PROMO_HEADAR.ID

tinyint

Always value 0 when downloading promo from ERP

numeric(5,2)

Corresponds to BENEFIT_PERC in PROMO_HEADER table

nvarchar(100)

Corresponds to NAME in PROMO_HEADER table

tinyint

Corresponds to MECHANIC in PROMO_HEADER table

smallint

Corresponds to APPROVED_BY in PROMO_HEADER table

date

Corresponds to UPDATED_DATE in PROMO_HEADER table

smallint

Corresponds to UPDATED_BY in PROMO_HEADER table

smallint

Corresponds to CREATED_BY in PROMO_HEADER table

char(1)

Corresponds to TYPE in PROMO_HEADER table

varchar(20)

Corresponds to COMMUNICATIONS in the PROMO_HEADER table

nvarchar(256)

Corresponds to REMARKS in PROMO_HEADER table

datetime

NOT NULL

Y

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

int

Order identifier / number created by MySales

smallint

Store identifier from STORES.STORE_ID

*

STORES.CLIENT_CODE

smallint

Supplier identifier from SUPPLIERS.SUPPLIER_ID

*

SUPPLIERS.CLIENT_CODE

smallint

Contract identifier from CONTRACTS.CONTRACT_ID

*

CONTRACTS.CLIENT_CODE

smallint

Warehouse identifier from STORES.STORE_ID

*

STORES.CLIENT_CODE

int

Оnly for cross-dock store orders.

Warehouse order identifier / number for cross-dock combined order

int

Оnly for push orders.

Push identifier / number RPM_PUSH.ID

int

Оnly if the order with the additional load period.

Additional load identifier / number from RPM_ADD.ID

date

Order Date

date

Planned Delivery Date

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

varchar

Order identifiers / numbers of stores cross-dock warehouse order

int

Product identifier from PRODUCTS.SKU_ID

*

PRODUCTS.CLIENT_CODE

smallint

Manager identifier for the product / store from
EXT_MANAGER.ID

EXT_MANAGER.CLIENT_CODE

number(11,2)

Order Quantity

datetime

Last modified order date