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 the meta-data for documentation.

User guide

Install LSSC and run the LSSC Editor.

LSSC Editor includes 9 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.

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 consist 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.

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
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 parameter do not appear in your query, then your are doing something wrong.

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 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.

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.

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.