Quantity in stock in stock locations in Exact Online

Question

Using the following query, I found that for items that have a stock location, there are multiple rows returned from the REST API StockLocations of Exact Online:

select spn.item_code_attr || '-' || spn.warehouse_code_attr || '-' || stn.code key 
,      itm.itemgroupcode
,      itm.itemgroupdescription
,      spn.item_code_attr
,      spn.item_description
,      spn.currentquantity
,      spn.planning_in
,      spn.planning_out
,      spn.currentquantity + spn.planning_in - spn.planning_out plannedquantity
,      -1 bestelniveau /* out of scope */
,      itm.costpricestandard costprijs
,      itm.costpricestandard * spn.currentquantity stockvalue
,      spn.warehouse_code_attr
,      stn.code locatie
,      itm.unitcode UOM
, itm.id
, whe.id
, sln.stock
, sln.itemid
, sln.warehouse
, stn.id
from   exactonlinexml..StockPositions spn
join   exactonlinerest..items itm
on     itm.code = spn.item_code_attr
and    itm.code = 'LE-10242'
and    itm.isstockitem = 1
join   exactonlinerest..warehouses whe
on     whe.code = spn.warehouse_code_attr
left 
outer
join   exactonlinerest..stocklocations sln
on     sln.itemid = itm.id
and    sln.stock != 0
and    sln.warehouse = whe.id
left
outer
join   storagelocations stn
on     stn.id        = sln.storagelocation
and    stn.warehouse = sln.warehouse
--
-- Filter out no stock nor planned.
--
where  ( spn.currentquantity !=0
         or     
         spn.planning_in != 0
         or     
         spn.planning_out != 0
       )
and    spn.item_code_attr = 'LE-10242'
order 
by     key

For example, for this item, there are 10 StockLocations. When I sum the field Stock, it returns the stock quantity found in StockPositions. However, it seems that every transaction creates an additional StockLocation entry.

I would expect StockLocation to contain per location in stock the total amount to be found there.

EDIT

The StockLocations API is described in https://start.exactonline.nl/api/v1/{division}/logistics/$metadata as:

<EntityType Name="StockLocation">
  <Key>
    <PropertyRef Name="ItemID"/>
  </Key>
  <Property Name="ItemID" Type="Edm.Guid" Nullable="false"/>
  <Property Name="Warehouse" Type="Edm.Guid" Nullable="true"/>
  <Property Name="WarehouseCode" Type="Edm.String" Nullable="true"/>
  <Property Name="WarehouseDescription" Type="Edm.String" Nullable="true"/>
  <Property Name="Stock" Type="Edm.Double" Nullable="true"/>
  <Property Name="StorageLocation" Type="Edm.Guid" Nullable="true"/>
  <Property Name="StorageLocationCode" Type="Edm.String" Nullable="true"/>
  <Property Name="StorageLocationDescription" Type="Edm.String" Nullable="true"/>
</EntityType>

Somehow it is not documented at https://start.exactonline.nl/docs/HlpRestAPIResources.aspx

What am I doing wrong?

Uhm, that [table doesn't exist in the API](https://developers.exactonline.com/#RestRefDocs.html%3FTocPath%3DExact%2520Online%2520REST%2520API%7C_____1). Where did you get that from? — Patrick Hofman, Nov 07 '16 at 16:10

score 0 · Accepted Answer · answered Nov 18 '16 at 15:57

Discussed question on Hackathon with engineer. This is as the StockLocation API works; the naming does not optimally reflect the contents, but this is intended behaviour.

With a select field, sum(stock) from stocklocations group by field, you can get the right information.

To improve join performance, it is recommended to use an inline view for this such as select ... from table1 join table2 ... join ( select field, sum(stock) from stocklocations group by field).

Quantity in stock in stock locations in Exact Online

EDIT

1 Answers1