ERP integration

VidiStock can import product, sales, stock and purchasing data from your ERP automatically. The connection only reads; it never writes anything to your ERP.

Beta

The Netsis, Logo Tiger / Go, Mikro and DİA connections are in beta: they have been verified against test environments, and verification in real customer installations is ongoing. During the first setup, compare the figures with your ERP reports.

How does it work?

A small service called VidiStock Connector is installed on a computer in your network. It reads the data and sends it to VidiStock. The connector always opens the connection, so you do not need to open any inbound port in your firewall.

Netsis, Logo and Mikro: the connector does not touch the ERP tables. It reads from reading objects (views or procedures) installed in a separate schema called vidistock in the ERP database. The connector’s SQL user has permission only on this schema and none on the ERP tables.

• How each piece of data is read is written in these objects. Examples are whether invoices or dispatch notes are read, which branch is included and how campaign sales are marked.
• The objects are installed during setup by the integrator, starting from the sample scripts and adapting them to how the company uses its ERP.
• If something changes in the ERP, only these objects are edited; nothing changes in VidiStock.

DİA: because it runs in the cloud, the connector reads DİA through its web service. It can run on any computer with internet access.

Setup steps

The Administration → Integrations page is available to administrators only.

1. Choose the ERP: select the ERP you use.

2. Connector: click Generate key and copy the key; it is shown only once. Install the connector and enter the VidiStock address and the key in its settings file. Within a minute the “Connector online” badge appears.

3. Reading objects (Netsis, Logo, Mikro): get the scripts with Download sample scripts on the page. Together with your database administrator, run them in the ERP database in this order:

   • 00-sema-ve-yetki.sql: creates the vidistock schema and a SQL user with permission only on this schema.
   • views.sql or procedures.sql: installs the reading objects. First edit the places marked UYARLAYIN to fit the company.
   • You can choose a view or a procedure for each data type, and mix the two.

4. Connection: enter the server and name of the ERP database and the SQL user from step 3. Choose the object type (view or procedure) you installed for each data type, then click Save and test the connection. The test checks:

   • that each object exists and has the right type;
   • the columns the objects return;
   • the user’s permissions.

   For DİA you enter the server code, DİA user, API key and firm code instead.

5. Scope and schedule: set which data is imported, how often, and the warehouse mapping.

6. Enable the integration and click Sync now.

Reading objects

Objects are named vidistock.<Name>; the schema name can be changed on the Integrations page.

Column rules:

• Columns are matched by name; case and order do not matter.
• Extra columns are ignored.
• A missing optional column counts as empty.

Object	Required columns	Optional columns
Warehouses	Code	Name
Products	Code, Name	Unit, Category, SupplierCode, SupplierName, LeadTimeDays, Cost, Price, CostCurrencyNo, PriceCurrencyNo, product attributes
Sales	ProductCode, Date, SalesQty	WarehouseCode, CustomerCode, CustomerName, ReturnQty, CampaignQty
Stock	ProductCode, OnHand	WarehouseCode, OnOrder
Suppliers	Code	Name
Deliveries (purchase orders)	SupplierCode, ProductCode, OrderDate, OrderedQty	OrderNo, DueDate, ReceivedDate, ReceivedQty, UnitPrice, CurrencyNo, WarehouseCode, Closed
Transfers (between warehouses)	Date, ProductCode, FromWarehouse, ToWarehouse, Qty	DocumentNo

Date filter and parameters:

• For a view, the connector applies the date filter:
  • for sales and transfers, Date must be within the range being read;
  • for purchasing, OrderDate must be after the start of the purchasing history.
• A procedure applies the same filter with its own parameters:
  • @From date, @To date for sales and transfers (both days included);
  • @PurchasingFrom date for stock, suppliers and purchasing.

Totals and conversions:

• Sales and transfers can be returned line by line; VidiStock totals them per day, product and warehouse.
• Quantities must be in the base unit.
• CampaignQty is the part of a sale marked as campaign. It is removed from the baseline demand forecast and campaign effects are learned from it.

The detailed meaning of the columns is in the README.md file that comes with the sample scripts.

Settings

Setting	Description
Schema	The schema that holds the reading objects (default vidistock). If it changes, the sales history is read again from the start in the next sync.
Reading objects	A view or a procedure for each data type. There is no default: the integration cannot be enabled until one is chosen for every data type in scope. “Not read” can be chosen for transfers. If the object type for sales changes, the sales history is read again from the start.
History start	The first date from which sales are read in the first sync. At least two years is recommended.
Refresh window	The number of recent days read again in each sync. Documents corrected or deleted in the ERP within this window are reflected in VidiStock.
Schedule	Every day at a set hour, at a fixed interval, or manually only.
Sales source (DİA)	Whether sales quantities are read from invoice or dispatch note movements. For Netsis, Logo and Mikro this choice is part of the sales object.
Campaign sales (DİA)	If you mark campaign sales in a field of the sales line, the field name and value (default K). For Netsis, Logo and Mikro, campaign sales are read from the CampaignQty column of the sales object.
Warehouse mapping	The VidiStock warehouse for each ERP warehouse. Data for unmapped warehouses is not imported; do not map scrap or waste warehouses.

ERP-specific notes

• Netsis:
  • The sample scripts read sales from sales invoices and count customer return invoices (incoming movements of the purchase type, movement type L and M) as returns. There is no branch filter; add one to the sales object if needed.
  • Netsis databases usually have a Latin1 collation but store text in the Turkish code page. The vidistock.TRK function in the scripts fixes the letters İ, ı, Ş, ş, Ğ, ğ in names.
  • Netsis compares codes case-insensitively (51011P and 51011p are the same); VidiStock also matches a code without an exact match regardless of case, if it fits only one product.
• Logo Tiger / Go:
  • Tables carry the firm and period number. The firm number and sales source are in the settings section at the top of the sample scripts.
  • Movements are read from all of the firm’s periods; stock and latest prices come from the latest period.
  • views.sql builds the views with the periods that exist when it runs, so run it again when a new period is opened. procedures.sql finds the periods on every read.
• Mikro: The stock balance is calculated from the sum of movements; in very large databases reading stock can take a while.
• DİA:
  • Create a separate DİA user for the web service; using the same user as someone working in DİA can end their session.
  • Each sync uses DİA credits; for most companies one sync a day is enough.
  • Product cost, sales price and lead time are not imported from DİA.

Orders, deliveries and transfers

Purchase orders, goods receipts and transfers between warehouses are done in the ERP. VidiStock only reads them and compares them with its suggestions (Suggestion compliance); the same transaction is not entered twice in VidiStock.

• Orders and deliveries are read when the purchasing scope is enabled.
  • The order’s warehouse is read too and used to match orders with suggestions per warehouse.
  • An order whose ERP warehouse is not mapped is still imported, with an empty warehouse.
  • For DİA the warehouse is read when available.
  • Orders from the last 400 days are read, plus those still open regardless of their date. A partly received line stays open and the remaining quantity is expected.
  • An order closed in the ERP (Closed; KAPATILMIS in Netsis): if partly received, it shows as Closed short on the Purchase Orders page and reopens if it is reopened in the ERP; if closed without any receipt, it is not imported.
• Transfers are read when the stock scope is enabled; those with both warehouses mapped are imported. They are read again as far back as the refresh window, and for the last 400 days in the first sync. In the sample scripts transfers are found as follows:
  • Mikro: transfer movements between warehouses (source and destination on the same line).
  • Logo: warehouse slips; the outgoing line of the slip is read.
  • Netsis: an outgoing movement from one warehouse and an incoming movement to another under the same slip number.
  • DİA: not read in this version; upload transfers with the transfer file under Import.

After the first sync

For a few products, compare last month’s sales and the warehouse balance with your ERP report. If you see a difference:

• Netsis, Logo, Mikro: correct the reading object concerned. The query lives in the ERP database, so you can run and test it directly in SQL Server Management Studio.
• DİA: disable the integration and report it to the support team.

Foreign currency prices

The currency type number in the ERP is matched with the currency sequence number on the Settings page; 0 is always Turkish lira. If the numbers differ, enter the numbers your ERP uses in Settings. If the ERP also uses another number for the same currency (for example Turkish lira as 6 in the Netsis currency definitions), enter it under the currency’s Other ERP numbers. Netsis keeps its currency definitions in the KUR table of its system database (SIRA = currency type).

• Products: if CostCurrencyNo and PriceCurrencyNo are filled in the Products object, the price is imported in that currency; if 0, in Turkish lira. If empty, the product keeps its current currency.
  • The Netsis sample script reads the currency type and foreign price on the stock card.
  • The Logo and Mikro samples do not read currencies.
• Undefined number: if the number from the ERP is not defined in Settings, the row is not rejected; the record is treated as Turkish lira and the sync history shows one warning per number (with the number of rows). It is fixed in the next sync after you define the number.
• Purchase orders: an order appears in VidiStock in the currency it has in the ERP (UnitPrice and CurrencyNo in the Deliveries object). In the sample scripts the currency comes from:
  • Netsis: the currency type and foreign price on the order line.
  • Mikro: the order’s currency.
  • Logo: the line’s transaction currency and rate; the local price is divided by the rate. Logo’s currency numbers differ (e.g. euro is 20); enter the sequence numbers accordingly in Settings.
• DİA: product card prices are read in local currency. The order currency is not read yet; DİA orders are treated as TRY.

Product attributes

If an attribute’s ERP field is filled on the Settings page, the connector reads that field and writes the value to the product.

• Netsis, Logo, Mikro: the ERP field is the name of an extra column of the Products object. The sample scripts include Netsis KOD_1 and KOD_2, Logo SPECODE and Mikro sto_marka_kodu. If you need another field, add it to the object as a column; a value kept in a separate table can also be joined into the object.
• A column that is not found appears in the connection test as “optional column not found”; the sync does not stop and the attribute stays empty. The field name may contain only letters, digits and underscores.
• For a multi-value attribute, the ERP value is split by commas.
• An attribute fed from the ERP is overwritten with the ERP value in every sync; manual changes on the product page do not last.
• DİA is not supported yet.

Connector version

Reading from reading objects comes with connector version 2.0. Earlier connectors read the ERP tables directly. If an older connector is installed:

• the Integrations page shows an update warning;
• the connector receives no settings or commands until it is updated.