Discuss (0)

Print

RSS

Products » Safety Stock Calculator » Here

Editor for LSSC inventory adapters

LSSC Editor is a utility application bundled with LSSC. This editor lets you edit and test SQL queries that are used to plug a database-based application into LSSC.

Key target applications:

• ERP
• eCommerce
• accounting
  

Big picture

LSSC is an inventory reporting application. It helps retailers and manufacturers to optimize their stock levels. LSSC includes an extensible framework to import data from 3^rd party applications.

This framework is based on a XML format, named LSSC-XML, that wraps SQL queries. Those queries are used to import inventory data into LSSC. Thus, any database-based 3^rd party application can be supported by LSSC if the corresponding LSSC-XML file is provided.

Key features

• easy integration of custom app into LSSC.
• open and save LSSC-XML files.
• edit the SQL queries.
• test the SQL queries against your database.
• add meta-data for documentation.
  

User guide

Table of Contents [Hide/Show] Editor for LSSC inventory adapters    Big picture    Key features User guide       General panel       SKU panel       Products panel       Orders panel       Stock on hand panel       Suppliers panel       Lead times panel       Service levels panel       Reorder points panel       Tags panel       Events panel       Connection option Importing the adapter into LSSC    Importing the adapter in LSSC version 2.3 and after    Importing the adapter in LSSC version 2.2 and before

Install LSSC and run the LSSC Editor.

LSSC Editor includes following panels:

• General: meta-data about the LSSC-XML file itself.
• SKU: select the SKU names.
• Product names: select the product names.
• Orders: select the demand orders with paging.
• Stock at hand: select inventory quantities.
• Suppliers: select suppliers.
• Lead times: select lead times.
• Service factors: select service factors.
• Reorder points: update reorder points.
• Tags: select tag descriptors with paging.
• Events: select events with paging.
  

Only the panels indicated in bold are required, all the other queries are optional. Thus, a minimal LSSC-XML file contains only two SQL queries.

The first step consists in selecting a database type and then entering the database connection string. This connection string will not be stored in the LSSC-XML file. It is only provided to enable live query validation against your database.

General panel

This panel contains the meta-data. We suggest to include precise information about the environment such as the application version number and database version number that you are currently using.

The connection option will be discussed in a later section below. Concerning the connection option help, this text box must stay empty unless you are actually requesting from the user to provide a connection option.

SKU panel

SKU means storage keeping unit. In the case of a multi-location inventory, the same product reference might be associated to several SKUs.

This panel includes a query to retrieve a list of pairs (SKU identifiers, SKU names). The expected query should return two fields

SELECT
  SkuIdentifier,
  SkuName
FROM
  SkuTable

Some applications do not support the notion of SKU (typically eCommerce). Instead the database only contains the list of products. In such situation, the product identifiers and and product names could be returned instead of real SKUs.

LSSC is distinguishing SKU identifiers from SKU names because the SKU identifiers are actually transmitted to Lokad by LSSC through our web services. Since the SKU names may contain sensitive information, users usually prefer non-readable database identifier to be used instead.

If there is no way to distinguish SKU identifiers from SKU names (this situation happens when the SKU names are used as primary keys in the database), then the very same field can be returned twice (both as identifier and name).

Click on the button Validate query once you have entered your custom SQL query. If the query is correctly executed, the returned dataset is displayed on upper right block. On the contrary, if the query fails, the error message is displayed on the lower right block.

All the following panels are following the same design.

Products panel

The products panel includes a query used to retrieve all the product names associated to the SKU identifiers, i.e. as retrieved by the query of the previous panel. This query is optional, thus you can leave it blank in the editor.

The expected query should return two fields

SELECT
  SkuIdentifier,
  ProductName
FROM
  SkuTable

If the SKU names are already product names (i.e. no SKU support in the 3^rd party application), then we suggest to leave this query blank in order to avoid the product names to appear twice in the LSSC report.

Again, once you have entered your query, click on Validate query to be sure that it works.

Orders panel

This panel is used to retrieve all inventory movements, i.e. orders and very frequently sales orders. The semantic associated with an inventory movement slightly depends of the business area. For an eCommerce application, an order nothing but a sales order. For a manufacturing ERP, an order can also be internal item consumption for production.

The main issue with orders retrieval is the potentially very large amount of data to be retrieved. If we were trying to retrieve all the data at once, the query would very likely encounter a database timeout even if there was not that much data to be retrieved.

Thus, LSSC adopts a classical query paging scheme where an index and a page size are specified. In addition, the query includes two date constraints: start time (inclusive) and end time (exclusive).

The order panel is slightly more complicated than the two previous ones, indeed there are 4 query parameters. The expected query must return 3 fields:

SELECT
  SkuIdentifier,
  OrderDate,
  OrderQuantity
FROM
  OrdersTable
WHERE
  OrderDate >= ?startDate AND
  OrderDate < ?endDate
ORDER BY
  SkuIdentifier
LIMIT
  ?index, ?pageSize

Those parameters are input parameters, they will be provided by LSSC to retrieve the data in an incremental fashion. Those parameters should be consumed by the specified query. If those parameters do not appear in your query, then the query will not pass the validation.

The SQL query parameters are usually prefixed by a special character that depends of the database type:

• MySQL parameters are prefixed with ?
• MSSQL parameters are prefixed with @
• Oracle parameters are prefixed with :
  

Performance tip: daily aggregation of orders. A way to significantly improve the retrieval performance of LSSC consists in pre-aggregating the orders. Basically all the orders from the same day for the same SKU can be summed. Daily aggregation has no side effect on LSSC, because the first operation after orders retrieval is precisely daily order aggregation .

Stock on hand panel

The stock on hand is the amount of inventory readily available. This query is optional and can be left blank.

The query is very similar to the product name query. It must return two fields:

SELECT
  SkuIdentifier,
  StockOnHand
FROM
  SkuTable

The StockOnHand field is expected to be a numerical type.

Suppliers panel

Suppliers are very useful when it comes to process the replenishments. Indeed, SKUs can be sorted against their respective suppliers. This query is optional and can be left blank.

The query is very similar to the product name query. It must return two fields:

SELECT
  SkuIdentifier,
  Supplier
FROM
  SkuTable

Lead times panel

The lead time is the total delay between the reorder and the actual inventory replenishment. This query is optional and can be left blank.

The query is very similar to the product name query. It must return two fields:

SELECT
  SkuIdentifier,
  LeadTime
FROM
  SkuTable

The LeadTime field is expected to be a numerical type. The value must be expressed in days.

Service levels panel

The service level is a percentage that expresses the probability of not encountering a shortage for a particular SKU. This query is optional and can be left blank.

This query is very similar to the product name query. It must return two fields:

SELECT
  SkuIdentifier,
  ServiceLevel
FROM
  SkuTable

The ServiceLevel field is expected to be a numerical type. The value must comprised between zero and one.

Reorder points panel

The reorder point is the amount of inventory that should trigger a replenishment order if the stock on hand gets below this threshold. LSSC computes reorder points using demand forecasts. The reorder points, as computed by LSSC, can be exported to the 3^rd party application.

This query is typically an UPDATE following the pattern:

UPDATE SkuTable
SET
  ReorderPoint = ?reorderPoint
WHERE
  SkuIdentifier = ?skuIdentifier

The ReorderPoint is expected to be an numerical field.

At this point, the editor can execute a sample UPDATE query, but it cannot check that the update is correct. Thus, you have to make sure, for example through the GUI of the 3^rd party application, that the data has been correctly updated.

Tags panel

Introduced in version 1.9

Tags are used as descriptors for the SKUs in order to help Lokad understanding the similarities that exist between products. This query is optional and can be left blank.

This query is very similar to the product name query. It must return two fields:

SELECT
  SkuIdentifier,
  Tag
FROM
  SkuTable
LIMIT
  ?index, ?pageSize

Yet, the query comes with paging pretty much like the Orders query. Note that multiple tags can be returned for a single SKU.

Events panel

Introduced in version 1.9

Information about events (i.e.: promotions or inventory shortages) could also be used to help Lokad provide better forecasts. This information is optional and the panel could be left blank.

Any event can be described for Lokad by following properties:

• Name - identifier or description for the event.
• Time - time at which this event took place.
• Duration - duration of the event in days (could be 0).
• Known Since - optional argument to identify unexpected events like force-major situations.
  

Events panel contains optional settings to configure retrieval of this data from the database. Any query must return following fields:

SELECT
  SkuIdentifier,
  EventName,
  EventTime,
  EventDuration,
  EventKnownSince
FROM
  SkuEventTable
LIMIT
  ?index, ?pageSize

SKU can hold any number of events.

Connection option

The connection option is a secondary string (the database connection string being the primary one) provided by the user. Typically, most 3rd party applications do not need a connection option to be specified. In such situation, make sure that the connection option help text box is left empty, otherwise, the user will be asked to enter a connection option.

The connection option is provided to handle a couple of scenarios that do not fit into the "static" SQL adapter framework, in particular

• multi-company ERP: SQL queries are taking an extra parameter to specify the company.
• table name prefixes: SQL table names are prefixed, the prefix is dependent of the installation.
  

In order to support those scenarios, the SQL adapter framework provides a simple string-replacement system. The connection option string contains the list of replacements to be made in the SQL commands, with the following syntax

KEY1=VALUE1;KEY2=VALUE2;KEY3=VALUE3;

Notice that each replacement pattern gets separated by semi-colon ';' - then all KEY token get replaced by their respective VALUE token, key and value being separated by the equal sign '='.

Importing the adapter into LSSC

Now that you have created an LSSC-XML file, you need to reference this adapter into the LSSC application settings in order to make it available within the application.

Importing the adapter in LSSC version 2.3 and after

Step 1: Open extensions folder by clicking "Tools | Locate Extensions Folder" from the LSSC Menu.

Step 2: Copy your LSSC-XML file to that folder. Let's say it was named MyFirstAdapter.xml.

Step 3: Locate SafetyStockAdapters.ladx file and open it with your favorite XML editor. You can use Notepad or Notepad++ which is a free editor with XML editing capabilities.


Step 4: Add following XML element between the lssc-adapters elements and then save:

<adapter 
    id="MyFirstAdapter" 
    definition="MyFirstAdapter.xml" 
    factory="{XmlFactory}" />

You only need to change values of id and definition attributes to match your adapter, where:

• id - unique string without spaces (it should be unique for every adapter);
• definition - a relative path to the LSSC-XML file.
  

Step 5: Restart the LSSC application. Your new adapter should now be visible within the adapters list. Should there be any issues or warning while loading this adapter, they will be displayed in the separate window.

Optional Step 6: in order to redistribute LSSC adapters you need to copy LADX file and LSSC-XML (and other, if applicable) files that it references to the Extensions folder on another machine.

Importing the adapter in LSSC version 2.2 and before

Open the installation folder of LSSC (usually it's C:\Program Files\Lokad Safety Stock Calculator, and look for the file named Lokad.SafetyStock.Windows.exe.config. This configuration file is an XML file that contains the list of available LSSC-XML adapters.

Open Lokad.SafetyStock.Windows.exe.config within a text editor (you can use Notepad or Notepad++ which is a free editor with XML editing features).

Tip: If you happen to break the config file by mistake, you can re-download the file at this location. This should be easier as opposed to reinstall the whole application.

The first important section is /configuration/castle/components which contains a component with id="inventoryAdapterCollection". The XML section contains an array that lists all the adapters.

<array>
  <item>${TSV}</item>            
  <item>${osCommerce}</item>
  <item>${CubeCart3}</item>
  ...
</array>

Just add your own <item>${myAdapterId}</item> line. Then, below you can insert a new component block such as

<component id="myAdapterId"
  service="Lokad.SafetyStock.IInventoryAdapterFactory, Lokad.SafetyStock.InventoryAdapter"
  type="Lokad.SafetyStock.XmlInventoryAdapterFactory, Lokad.SafetyStock.InventoryAdapter">
  <parameters>
    <UniqueIdentifier>myAdapterId</UniqueIdentifier>
    <DefinitionFilePath>xml\myAdapter.xml</DefinitionFilePath>
  </parameters>
</component>

Note that id="myAdapterId" must match the line <item>${myAdapterID}</item>; and obviously xml\myAdapter.xml must correctly reference your LSSC-XML file (we suggest to put this file into the \xml directory).

The value <UniqueIdentifier>myAdapterId</UniqueIdentifier> is not required to exactly match the component id="myAdapterId" identifier. This UniqueIdentifier value is used within LSSC (the main application, not the editor) as an adapter identifier when recording the report settings. Consequently, if you were to change this identifier, previously saved LSSC reports would have to be reconfigured because the adapter would be considered as a different adapter.

After completing those changes, save the config file and launch LSSC. In the setup wizard, your new adapter should now appear.