Stock queries

Guidelines

Creation of a new query

To create a new query, click on the button “Add a stock location query”.

You'll need to give it a name, and then choose whether the query should inherit from another existing query or not.

If you choose to inherit from an existing query, you won't be asked for any additional parameters, as you'll get back all those set on the parent query.

However, if you choose to create a query without inheritance, you will be prompted for export and unification parameters. These parameters can be modified after creation, and a more detailed explanation is given in the following documentation.

Aggregates

A stock query is a sum of aggregates representing a scope of stock locations on particular items, for which some elements are deducted. Then, settings adjust the response according to specific needs. Here is a diagram of a stock request.

Each aggregate represents a scope of stock locations on a scope of items. All aggregates are added together, so no stock location must be returned in two different aggregates, otherwise the stock response will be doubled.

When creating an aggregate, you need to define a name and then specify which stock locations and item queries will be used to represent the aggregate.

Stock locations and items queries

Once selected at the creation, queries are visible by unfolding the aggregate. The "Edit" button is used to modify the query and also to change the parameter “force usage of the filter requested” (details below). There's also a redirect button that opens another window on the item or stock location query.

Force usage of stock location/item filter requested.

When selecting stock locations and items queries, you must choose whether or not to activate it. It is important to understand this parameter, as it can have an impact on the results of the stock query.

When users uses the API GET /stock_export route, they won't necessarily want to retrieve all possible values, but only on particular items or stock locations. They can therefore use filters as shown below.

GET /stock_export
{
    "site_id": "cxxx",
    "request_name": "newquery",
    "aggregates": {
        "*": {
            "item_filter": {
                "ids": [
                    "ITEM_001"
                ]
            },
            "endpoint_filter": {
                "ids": [
                    "STORE_001"
                ]
            }
        }
    }
}

If the Force usage of stock location/item filter requested parameter is enabled, filters will be taken into account. Otherwise, they will be ignored and all results will be returned.

Stock type filter

In most cases, there is only one type of stock set up in Onestock that represents available stock, so this filter can be ignored. But if the project uses several types of inventory (to manage future stocks, for example) this parameter can be useful, as it will only return the stock types you're interested in.

You can even filter on periods of future stock availability, as shown below.

Deductions

Deductions are elements that will be decremented from the stock results of the aggregate. We recommend that you activate the following 4 deductions:

• Types of unavailability: There are different types of unavailability declared in store, depending on the reason given by the vendor. You must indicate the types you wish to deduct from the available stock. We advise you to select the not_in_stock type.

• Stock locations reservations: You need to Indicate the sales channels on which store reservations are to be taken into account.

• Global buffers: You need to indicate the global buffers to be deducted. If several buffers are selected, the one with the highest value will be applied.

• Stock locations buffers: You need to indicate the stock locations buffers to be deducted. If several buffers are selected, the one with the highest value will be applied.

Settings

Once the sum of the stock aggregates has been achieved, we can adjust 4 different parameters to obtain an appropriate stock response.

Global reservations

This parameter is used to deduct global item reservations from the sum of aggregates. Global reservations are generally created in Onestock when the order is created, and disappear when the items are claimed, to be transformed into stock locations reservations.

In particular cases, you can choose to take into account global reservations only on a list of sales channels, but in general you should leave the "All sales channels" parameter checked.

Unification

The unification adds up all stock lines and returns a single global value. This setting is very important as it will completely change the format of the stock response.

The most common form of unification is by stock locations, but you can also unify by stock type if you have more than one configured on your project.

If the query is unified by stock locations, then the "unified" badge will be visible in the main page tree. Otherwise, the badge will be "detailed".

Stock export

This setting allows to configure the “export” queries used to update stock availability on the website.

• Diff/Full: If the "Full" parameter is checked, all stocks returned by the query will be sent. If "Diff" is checked, exported stocks are those that have changed since the last export on the same scope. As the full export is very large, it is usually set to be sent once in the morning. Then, several diff exports are sent at regular intervals during the day.

• Scope key: This key, mandatory to perform diff exports, is a freely definable id which identifies a stock perimeter on which the export will be done. This can be a sales channel, a country, etc... When a diff stock is exported, only movements that have taken place since the last export on the same key are counted. Once exported, the counters are reset to 0 for this key, but not for the others.

• Export 0 for unreferenced items: If this parameter is enabled, when a stock line is unreferenced, a quantity of 0 will be returned in the export following the unreferencing. An unreferenced stock line is one that no longer exists in the database, or for which's stock location or item is no longer included in the item or stock location queries. This feature requires a specific configuration at the site level to work, contact your OneStock point of contact for assistance.

• Export stock difference instead of raw value: If enabled, the values returned will represent the stock difference from the last export. This difference can be positive or negative. If deactivated, raw stock values will be exported.

Details

This final setting allows you to return more detailed results.

• Return details of deductions: deducted elements such as buffers, unavailabilities or reservations are returned.

• Return custom information: custom information sent during the stock import is returned.

Onestock advices

Basic queries

We advise you to create the basic queries, i.e. :

• detailed - this basic query is supposed to return all available stocks from all stock locations on all items. A single aggregate all can be created, which can refer to endpoints and items queries, and with all deductions set. Only the setting of global reservations can be added.

  • unified - the query inherits from detailed. Only the setting of unification (by stock type and by stock location) has to be enabled.

For orchestration

We recommend that you separate orchestration requests according to delivery type. In our example, we have 3 types of delivery:

• Ship From Store:

  • The orchestration_detailed_sfs query can be created at the root, containing two aggregates stores and warehouses which will be the two order fulfillment paths.
    - The stores aggregate should contains the items_sfs item query, which lists items eligible for order-taking, and the stores_sfs stock locations query, which lists stores with the Ship From Store module enabled.
    - The warehouses aggregate should also use the items_sfs item query, and the warehouses_sfs stock locations query, which lists warehouses with the Ship From Store module enabled.
    All aggregate deductions can be activated (buffers, reservations and unavailabilities) as well as the global reservations parameter.

    • The orchestration_unified_sfs can be created by inheriting from orchestration_detailed_sfs. Only the “Unification” parameter should be changed (unification by sock locations and stock types enabled).

    • orchestration_detailed_sfs_fr

      • orchestration_unified_sfs_fr

    • orchestration_detailed_sfs_uk

      • orchestration_unified_sfs_uk

Voir si exemple en photo

• Click and Collect express:

• Click and Collect standard:

For stock export

For the OIS catalog

For e-reservation

For delivery promise

Full stock queries tree

This is what our stock queries tree should look like.

• detailed

  • unified

• orchestration_detailed_sfs

  • orchestration_unified_sfs

  • orchestration_detailed_sfs_fr

    • orchestration_unified_sfs_fr

  • orchestration_detailed_sfs_uk

    • orchestration_unified_sfs_uk

• orchestration_detailed_ckc_exp

  • orchestration_unified_ckc_exp

  • orchestration_detailed_ckc_exp_fr

    • orchestration_unified_ckc_exp_fr

  • orchestration_detailed_ckc_exp_uk

    • orchestration_unified_ckc_exp_uk

• orchestration_detailed_ckc_std

  • orchestration_unified_ckc_std

  • orchestration_detailed_ckc_std_fr

    • orchestration_unified_ckc_std_fr

  • orchestration_detailed_ckc_std_uk

    • orchestration_unified_ckc_std_uk

• export_detailed_fr_full
  export_unified_fr_full
  export_detailed_fr_diff
  export_unified_fr_diff
  export_detailed_uk_full
  export_unified_uk_full
  export_detailed_uk_diff
  export_unified_uk_diff

• ois_detailed_fr
  ois_unified_fr
  ois_detailed_uk
  ois_unified_uk

• eresa_detailed_fr
  eresa_unified_fr
  eresa_detailed_uk
  eresa_unified_uk

• dp_detailed_fr_method1
  dp_unified_fr_method1
  dp_detailed_fr_method2
  dp_unified_fr_method2
  dp_detailed_uk_method1
  dp_unified_uk_method1
  dp_detailed_uk_method2
  dp_unified_uk_method2

• in_stock

(image KO)