Products »
Safety Stock Calculator » Here
Editor for LSSC inventory adapters

Click the image for more screenshots
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:
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.