Synchronisation General Board

This is an overview of what and when we synchronise data.

Entity	*Sync FROM* -> TO**	Frequency (min)
Products	MS SQL > OP	every 60 min
Supplier	MS SQL > OP	every 60 min
Supplier Products	MS SQL > OP	every 60 min
Sell Orders	MS SQL > OP	every 60 min
Buy Orders	MS SQL > OP	every 60 min
Receipt Lines	MS SQL > OP	every 60 min
Product Compositions	MS SQL > OP	every 60 min
Promotions	MS SQL > OP	every 60 min
Promotion Products	MS SQL > OP	every 60 min
Buy Orders	OP > MS SQL	every 10 min

Products

Optiply	MS SQL	Required
remoteId	The ID in the remote System	yes
name	string max length 255	yes
skuCode	string max length 255 - The SKU (Stock Keeping Unit) code that your own company uses
articleCode	string max length 255 - An article code that your own company uses that is not the SKU or the EAN
price	decimal max 9 integer digits, will round decimals to 2 places
unlimitedStock	boolean - By setting this boolean to `TRUE`, stock tracking can be disabled. Set this to `FALSE` for products you want to buy.	yes
stockLevel	integer, can be negative - The current physical level of stock minus already sold quantity. This calculation needs to be done in the Source system. Often called freeStock in other systems.	yes
status	enum - enabled or disabled
eanCode	Barcode - The EAN code used for selling this Product
notBeingBought	boolean - When `TRUE`, Stock level advice for this product will be 0. Product will effectively not be bought
created_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`
updated_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`(to be used as Replication Key)	yes
deleted_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`

Example:

SELECT yt.id as remote_id,

yt.big_name as name,

yt.sku as skuCode, yt.Code as articleCode,

yt.price as price,

yt.managedStock as unlimitedStock,

yt.stock as stockLevel, yt.status as status,

yt.EAN as eanCode, yt.created_at as created_at,

yt.updated_at as updated_at,

yt.deleted as deleted_at,

FROM your_table yt WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

Suppliers

Optiply	MS SQL	Required
remoteId	The ID in the remote System	yes
name	string max length 255	yes
emails	string[] - (exemples: `["example@example.com"]` or `["example@example.com";"example2@example.com"]`
deliveryTime	integer
created_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`
updated_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"` (to be used as Replication Key)	yes
deleted_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`

Example:

SELECT yt.id as remote_id,

yt.big_name as name,

yt.email as emails,

yt.deliveryTime as deliveryTime,

yt.created_at as created_at,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at,

FROM your_table yt WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

Supplier Products

Optiply	MS SQL	Required
remoteId	The ID in the remote System	yes
name	string max length 255	yes
skuCode	string max length 255
eanCode	string max length 255
articleCode	string max length 255
price	decimal max 9 integer digits, will round decimals to 2 places
minimumPurchaseQuantity	integer - The minimum quantity that you will purchase of this product if you purchase it. This is 1 by default and is only allowed to be greater than or equal to 1.
lotSize	integer - The lot size used when buying this product. E.g. when products are sold in sixpacks, the lotSize is 6. This is 1 by default and is only allowed to be greater than or equal to 1.
productId	long - The remoteId used in Products Table	yes
supplierId	long - The remoteId used in Products Table	yes
preferred	boolean - Indicates this is a preferred supplier - can you be set one per Product
status	enum - enabled or disabled. Use to mimic deletes.
deliveryTime	integer - Supplier lead time per supplier product (days)
created_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`
updated_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"` (to be used as Replication Key)	yes
deleted_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`

Example:

SELECT yt.id as remote_id,

yt.big_name as name,

yt.sku as skuCode,

yt.EAN as eanCode,

yt.Code as articleCode,

yt.price as price,

yt.moq as minimumPurchaseQuantity,

yt.lot as lotSize,

yt.pId as productId,

yt.sId as supplierId,

yt.preferred as preferred,

yt.status as status,

yt.deliveryTime as deliveryTime,

yt.created_at as created_at,

yt.updated_at as updated_at,

yt.deleted as deleted_at,

FROM your_table yt WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

Sell Orders

Optiply	MS SQL	Required
remoteId	The ID in the remote System	yes
placed	datetime with time zone - date on which the order was placed and is to be sent	yes
totalValue	decimal max 17 integer digits, will round decimals to 2 places	yes
updated_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"` (to be used as Replication Key)	yes
deleted_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`

Example:

SELECT yt.id as remote_id,

yt.created as placed,

yt.total as totalValue,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at,

FROM your_table yt WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

Sell Order Lines

Optiply	MS SQL	Required
remoteId	The ID in the remote System	yes
quantity	integer	yes
productId	long - The remoteId used in Products Table - ID of the product that's related with this sellOrderLine	yes
sellOrderId	long - The remoteId used in Sell Order Table - ID of the sellOrder that this sellOrderLine is part of	yes
subtotalValue	decimal max 17 integer digits, will round decimals to 2 places	yes
deleted_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`
updated_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"` (to be used as Replication Key)	yes

Example:

SELECT yt.id as remote_id,

yt.qty as quantity,

yt.productId as productId,

yt.sellOrderId as sellOrderId,

yt.subtotalValue as subtotalValue,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at,

FROM your_table yt WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

Buy Orders

Optiply	MS SQL	Required
remoteId	The ID in the remote System	yes
completed	datetime with time zone - Used to close the order. Fill when no products are left to be received.
placed	datetime with time zone - This field denotes the date and the time BuyOrder is placed.	yes
totalValue	decimal max 17 integer digits, will round decimals to 2 places	yes
updated_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"` (to be used as Replication Key)	yes
deleted_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`
reference	BuyOrders.id integer - this should only be filled for BuyOrders synced from Optiply to MS SQL.	Yes
supplierId	long - The remoteId used in Suppliers Table - ID of the supplierID that's related with this buyOrder	Yes

Example:

SELECT yt.id as remote_id,

yt.completed as completed,

yt.created as placed,

yt.total as totalValue,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at,

FROM your_table yt WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

Buy Order Lines

Optiply	MS SQL	Required
remoteId	The ID in the remote System	yes
quantity	integer	yes
productId	long - The remoteId used in Products Table - ID of the product that's related with this sellOrderLine	yes
BuyOrderId	long - The remoteId used in Buy Order Table - ID of the BuyOrder that this BuyOrderLine belongs to	yes
subtotalValue	decimal max 17 integer digits, will round decimals to 2 places	yes
created_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`
updated_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"` (to be used as Replication Key)	yes
deleted_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`
reference	BuyOrders.line_items.line_id integer - this should only be filled for BuyOrders synced from Optiply to MS SQL.	Yes

Example:

SELECT yt.id as remote_id,

yt.qty as quantity,

yt.productId as productId,

yt.BuyOrderId as BuyOrderId,

yt.subtotalValue as subtotalValue,

yt.created_at as created_at,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at,

FROM your_table yt WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

Receipt Lines

Optiply	MS SQL	Required
remoteId	The ID in the remote System	yes
quantity	integer	yes
buyOrderLineId	integer - ID of the BuyOrderLine that owns this ReceiptLine.	yes
occurred	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`	yes
deleted_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`
updated_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"` (to be used as Replication Key)	yes
reference	ReceiptLines.id integer - this should only be filled for ReceiptLines synced from Optiply to MS SQL.	yes

Example:

SELECT yt.id as remote_id,

yt.qty as quantity,

yt.buyOrderLineId as buyOrderLineId,

yt.occurred as occurred,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at,

FROM your_table yt WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

Product Compositions

Optiply	MS SQL	Required
Optiply	MS SQL	Required
remoteId	The ID in the remote System	yes
composedProductId	long	yes
partProductId	long	yes
partQuantity	integer - Must be at least 1.	yes
created_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"`
updated_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"` (to be used as Replication Key)	yes

Example:

SELECT yt.id as remote_id,

yt.composedProductId as composedProductId,

yt.partProductId as partProductId,

yt.qty as partQuantity, yt.created_at as created_at,

yt.deleted_at as deleted_at,

yt.updated_at as updated_at,

FROM your_table yt WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

Promotions

Optiply	MS SQL	Required
remoteId	The ID in the remote System	yes
name	string max length 255	yes
entireShop	boolean - POST only. Setting this to true will cause every product on the account to get added to the Promotion.
startDate	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"` Whatever time is set gets discarded as promotions are only supported on a “per-day” basis.	yes
endDate	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"` Whatever time is set gets discarded as promotions are only supported on a “per-day” basis.	yes
upliftType (*1)	“absolute”, “relative” or "close_out"
upliftIncrease (*1)	integer
enabled	boolean
updated_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"` (to be used as Replication Key)	yes

(*1) Conditions for upliftIncrease and upliftType:

If the upliftType is "close_out" then the upliftIncrease will be 0
if the upliftType is "relative" then the upliftIncrease must not be null

Example:

SELECT yt.id as remote_id,

yt.enabled as enabled,

yt.entireShop as entireShop,

yt.startDate as startDate,

yt.endDate as endDate,

yt.upliftType as upliftType,

yt.upliftIncrease as upliftIncrease,

yt.updated_at as updated_at,

FROM your_table yt WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

Promotion Products

Optiply	MS SQL	Required
remoteId	The ID in the remote System	yes
productId	boolean	yes
promotionId	long	yes
specificUpliftType (*1	“absolute”, “relative” or "close_out"
specificUpliftIncrease (*1)	integer
updated_at	datetime with time zone, format `"%Y-%m-%dT%H:%M:%SZ"` (to be used as Replication Key)	yes

(*1) Conditions for specificUpliftIncrease and specificUpliftType:

If the specificUpliftType is "close_out" then the specificUpliftIncrease will be 0.
if the specificUpliftType is not "close_out" then the specificUpliftType and specificUpliftIncrease must either both or neither be filled in.

Example:

SELECT yt.id as remote_id,

yt.productId as productId,

yt.promotionId as promotionId,

yt.specificUpliftType as specificUpliftType,

yt.specificUpliftIncrease as specificUpliftIncrease,

yt.updated_at as updated_at,

FROM your_table yt WHERE {replication_key_condition}

//replication_key_condition: {replication_key} >= {formatted_replication_key_value}

Optiply to MS SQL

Buy Orders from Optiply to MS SQL

A table will be automatically created in the customer's MS SQL named “BuyOrders”

Optiply		MS SQL
buyOrder.id		id
buyOrder.placed		placed
buyOrder.expectedDeliveryDate		delivery_date
supplier.remoteId		supplier_remoteId
supplier.name		supplier_name
buyOrderLines	buyOrderLine.id	line_items (JSON with the line attributes)	line_id
	products.remoteId		product_remoteId
	products.skuCode		product_sku
	buyOrderLine.quantity		quantity

Example:

MS SQL - Data mapping & Syncronisation

Synchronisation General Board

Products

Suppliers

Supplier Products

Sell Orders

Sell Order Lines

Buy Orders

Buy Order Lines

Receipt Lines

Product Compositions

Promotions

Promotion Products

Optiply to MS SQL

Buy Orders from Optiply to MS SQL