Warehouse stock, bins and allocations — Pro
Choose a warehouse; inspect products, stock components, bins, movement balances and allocations on demand.
DOCUMENTARY AND SYNTHETIC VALIDATION ONLY — not tested on a real Sage 200 installation. For warehouse staff, purchasing, sales and managers: choose a warehouse when opening the screen. Inspect each product with its group, type, current status, manufacturer, part number, barcode and stock/base units. See confirmed and unconfirmed stock, allocations to sales orders, Stock Control and bills of materials, reservations, quantity on purchase orders, minimum/maximum/reorder levels and last sale/count dates. Open the product’s bins, then the bin’s individual movement balances or allocations with their source or recipient, quantities and allocation dates. Zero, negative, inactive and unfamiliar native values are not silently hidden.
Why Cifru? Adapt configurations to the way you work. Choose the fields, filters and details you need, and bring information to your phone that may not be available in your business software’s own mobile app. Available options depend on the data exposed by your authorised source and your Cifru plan.
Pro: one SQL Server source, four lists, full Details and three on-demand related buttons; custom read-only SELECT. Warehouse selection uses the native opening filter; source reads apply that selection after reading the available choices. At most 2,000 rows per list/read; no periodic refresh. Lists and related reads may be incomplete at this limit. Local search/filters affect loaded rows only. Row limits do not guarantee an inexpensive query: review execution plans and timeouts with the database administrator. Required parent keys are carried as text and rechecked for warehouse, product, warehouse-item and bin before each child read. Navigation is not a permission boundary.
Quantity semantics: in stock = confirmed + unconfirmed; allocated = sales + Stock Control + BOM; unallocated = in stock − allocated, following the selected legacy guide. Reservations are shown separately, not deducted a second time. Unallocated quantity is NOT a guarantee that goods are sellable, pickable or available to promise. Warehouse sales-trading permission is only one condition; product rules, traceability, returns and other restrictions still apply. Each quantity uses that product’s stock unit. No sum across unlike units, warehouses or products is presented. Missing operands remain missing, not invented zeros. Movement balances are current stock components (opening minus issued), not a full dated movement ledger. Allocations are recorded bin allocations, not posted invoices or despatches. No financial valuation, currency, pricing, historical stock or reconciliation guarantee is claimed.
Unofficial, not affiliated with Sage. Physical identifiers come from the Sage 200 2015 database guide (November 2014), not a complete DDL or proof of compatibility with current versions. The guide contradicts itself on the Unconfirmed Return movement-state code; this template intentionally does not translate/filter that state. Modern Professional help explains usage only, not an unchanged physical schema. Requires the selected dbo objects in one authorised Sage 200 Professional UK company SQL Server database; verify installed schema, types, uniqueness, edition and read compatibility before use. Not Sage 200 Standard, Sage 200 Evolution or Sage 300. Direct SQL does not inherit Sage user roles or company permissions; use a separate least-privilege authorised SQL reader. No connection addresses, credentials or business rows are included. Native DEMO captures use wholly fictional local data, not a real SQL Server or Sage compatibility test.
Primary physical guide: https://desktophelp.sage.co.uk/sage200/PDF/2015/Understanding%20the%20Sage%20200%202015%20Database.pdf
Warehouse/bin usage: https://desktophelp.sage.co.uk/sage200/professional/Content/SC/WarehouseList.htm
Screenshots
What this package creates
1 Home3 Details4 lists1 sources to map
- Home: Warehouse stock
- Details: Bins
- Details: Movement balances
- Details: Allocations
- Sub-button: Stock bins
- Sub-button: Movement balances
- Sub-button: Stock allocations
Sources are mapped locally and verified before applying.
Custom queriesPRO4 SQL
Custom queries are a PRO feature. Cifru repeats read-only validation against the local source before execution.
Statically verified read-only
$.components.workspaceSelection.datasets.0.sqlQuerySELECT a.WarehouseItemID AS WarehouseItemKey, a.WarehouseID AS WarehouseKey, a.ItemID AS ItemKey, s.Code AS ItemCode, s.Name AS ItemName, s.Description AS ItemDescription, g.Code AS GroupCode, g.Description AS GroupName, CASE g.StockItemTypeID WHEN 0 THEN N'Stock' WHEN 1 THEN N'Miscellaneous' WHEN 2 THEN N'Service/Labour' WHEN 3 THEN N'Undefined' WHEN 4 THEN N'SOPItemDespatched' WHEN 5 THEN N'SOPItemNotDespatched' ELSE CASE WHEN g.StockItemTypeID IS NULL THEN N'Not recorded' ELSE CONCAT(N'Unknown native value: ',g.StockItemTypeID) END END AS ItemType, CASE s.StockItemStatusID WHEN 0 THEN N'Active' WHEN 1 THEN N'Inactive' ELSE CASE WHEN s.StockItemStatusID IS NULL THEN N'Not recorded' ELSE CONCAT(N'Unknown native value: ',s.StockItemStatusID) END END AS ItemStatus, CASE s.TraceableTypeID WHEN 0 THEN N'Non-traceable' WHEN 1 THEN N'Serial Numbered' WHEN 2 THEN N'Batch Numbered' ELSE CASE WHEN s.TraceableTypeID IS NULL THEN N'Not recorded' ELSE CONCAT(N'Unknown native value: ',s.TraceableTypeID) END END AS Traceability, s.Manufacturer AS Manufacturer, s.PartNumber AS PartNumber, s.Barcode AS Barcode, s.StockUnitName AS StockUnit, s.BaseUnitName AS BaseUnit, s.StockMultOfBaseUnit AS StockBaseMultiplier, w.Name AS WarehouseName, w.Description AS WarehouseDescription, CASE w.UseForSalesTrading WHEN 1 THEN N'Yes' WHEN 0 THEN N'No' ELSE N'Not recorded / unknown' END AS WarehouseSales, a.ConfirmedQtyInStock AS Confirmed, a.UnconfirmedQtyInStock AS Unconfirmed, (a.ConfirmedQtyInStock + a.UnconfirmedQtyInStock) AS InStock, a.QuantityAllocatedSOP AS AllocatedSales, a.QuantityAllocatedStock AS AllocatedStock, a.QuantityAllocatedBOM AS AllocatedBOM, (a.QuantityAllocatedSOP + a.QuantityAllocatedStock + a.QuantityAllocatedBOM) AS Allocated, ((a.ConfirmedQtyInStock + a.UnconfirmedQtyInStock) - (a.QuantityAllocatedSOP + a.QuantityAllocatedStock + a.QuantityAllocatedBOM)) AS Unallocated, a.QuantityReserved AS Reserved, a.QuantityOnPOPOrder AS OnPurchaseOrder, a.ReorderLevel AS ReorderLevel, a.MinimumLevel AS MinimumLevel, a.MaximumLevel AS MaximumLevel, a.DateOfLastSale AS LastSale, a.DateOfLastStockCount AS LastCount FROM dbo.WarehouseItem a INNER JOIN dbo.StockItem s ON s.ItemID=a.ItemID INNER JOIN dbo.Warehouse w ON w.WarehouseID=a.WarehouseID LEFT JOIN dbo.ProductGroup g ON g.ProductGroupID=s.ProductGroupID WHERE a.WarehouseItemID IS NOT NULL
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.1.sqlQuerySELECT a.WarehouseItemID AS WarehouseItemKey, a.WarehouseID AS WarehouseKey, a.ItemID AS ItemKey, b.BinItemID AS BinKey, b.BinName AS BinName, s.Code AS ItemCode, s.Name AS ItemName, s.StockUnitName AS StockUnit, w.Name AS WarehouseName, CASE b.UnspecifiedBin WHEN 1 THEN N'Unspecified bin' WHEN 0 THEN N'Specified bin' ELSE N'Bin setting unknown' END AS UnspecifiedBin, b.ConfirmedQtyInStock AS Confirmed, b.UnconfirmedQtyInStock AS Unconfirmed, (b.ConfirmedQtyInStock + b.UnconfirmedQtyInStock) AS InStock, b.QuantityAllocatedSOP AS AllocatedSales, b.QuantityAllocatedStock AS AllocatedStock, b.QuantityAllocatedBOM AS AllocatedBOM, (b.QuantityAllocatedSOP + b.QuantityAllocatedStock + b.QuantityAllocatedBOM) AS Allocated, ((b.ConfirmedQtyInStock + b.UnconfirmedQtyInStock) - (b.QuantityAllocatedSOP + b.QuantityAllocatedStock + b.QuantityAllocatedBOM)) AS Unallocated, b.QuantityReserved AS Reserved, b.DateOfLastStockCount AS LastCount FROM dbo.BinItem b INNER JOIN dbo.WarehouseItem a ON a.WarehouseItemID=b.WarehouseItemID AND a.ItemID=b.ItemID INNER JOIN dbo.StockItem s ON s.ItemID=a.ItemID INNER JOIN dbo.Warehouse w ON w.WarehouseID=a.WarehouseID WHERE a.WarehouseItemID=:warehouse_item AND a.WarehouseID=:warehouse AND a.ItemID=:item AND b.BinItemID IS NOT NULL
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.2.sqlQuerySELECT a.WarehouseItemID AS WarehouseItemKey, a.WarehouseID AS WarehouseKey, a.ItemID AS ItemKey, b.BinItemID AS BinKey, b.BinName AS BinName, s.Code AS ItemCode, s.Name AS ItemName, s.StockUnitName AS StockUnit, w.Name AS WarehouseName, CASE v.SourceAreaTypeID WHEN 0 THEN N'Supplier' WHEN 1 THEN N'Customer' WHEN 2 THEN N'Internal' WHEN 3 THEN N'Write Off' WHEN 4 THEN N'Opening Balance' WHEN 5 THEN N'BOM' ELSE CASE WHEN v.SourceAreaTypeID IS NULL THEN N'Not recorded' ELSE CONCAT(N'Unknown native value: ',v.SourceAreaTypeID) END END AS SourceArea, v.MovementID AS MovementKey, v.SourceAreaReference AS SourceReference, v.SourceAreaName AS SourceName, v.OpeningStockLevel AS OpeningQuantity, v.StockLevelIssued AS IssuedQuantity, (v.OpeningStockLevel - v.StockLevelIssued) AS RemainingQuantity FROM dbo.MovementBalance v INNER JOIN dbo.BinItem b ON b.BinItemID=v.BinItemID AND b.ItemID=v.ItemID INNER JOIN dbo.WarehouseItem a ON a.WarehouseItemID=b.WarehouseItemID AND a.ItemID=b.ItemID INNER JOIN dbo.StockItem s ON s.ItemID=a.ItemID INNER JOIN dbo.Warehouse w ON w.WarehouseID=a.WarehouseID WHERE a.WarehouseItemID=:warehouse_item AND a.WarehouseID=:warehouse AND a.ItemID=:item AND b.BinItemID=:bin AND v.WarehouseID=a.WarehouseID AND v.MovementID IS NOT NULL
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.3.sqlQuerySELECT a.WarehouseItemID AS WarehouseItemKey, a.WarehouseID AS WarehouseKey, a.ItemID AS ItemKey, b.BinItemID AS BinKey, b.BinName AS BinName, s.Code AS ItemCode, s.Name AS ItemName, s.StockUnitName AS StockUnit, w.Name AS WarehouseName, CASE v.SourceAreaTypeID WHEN 0 THEN N'Supplier' WHEN 1 THEN N'Customer' WHEN 2 THEN N'Internal' WHEN 3 THEN N'Write Off' WHEN 4 THEN N'Opening Balance' WHEN 5 THEN N'BOM' ELSE CASE WHEN v.SourceAreaTypeID IS NULL THEN N'Not recorded' ELSE CONCAT(N'Unknown native value: ',v.SourceAreaTypeID) END END AS SourceArea, v.AllocationID AS AllocationKey, v.RecipientReference AS RecipientReference, v.RecipientName AS RecipientName, v.AllocatedQuantity AS AllocatedQuantity, v.AllocationDate AS AllocationDate FROM dbo.AllocationBalance v INNER JOIN dbo.BinItem b ON b.BinItemID=v.BinItemID AND b.ItemID=v.ItemID INNER JOIN dbo.WarehouseItem a ON a.WarehouseItemID=b.WarehouseItemID AND a.ItemID=b.ItemID INNER JOIN dbo.StockItem s ON s.ItemID=a.ItemID INNER JOIN dbo.Warehouse w ON w.WarehouseID=a.WarehouseID WHERE a.WarehouseItemID=:warehouse_item AND a.WarehouseID=:warehouse AND a.ItemID=:item AND b.BinItemID=:bin AND v.WarehouseID=a.WarehouseID AND v.AllocationID IS NOT NULL
static read-only checks passed
View the JSON being importedcollapsed by default
{
"components": {
"sourceSlots": [
{
"displayName": "Sage 200 Professional UK — company SQL Server",
"id": "A946CAF9-7CEF-5438-B908-D303DD4B5E41",
"kind": "sqlServer",
"requiredObjects": [
"dbo.AllocationBalance",
"dbo.BinItem",
"dbo.MovementBalance",
"dbo.ProductGroup",
"dbo.StockItem",
"dbo.Warehouse",
"dbo.WarehouseItem"
],
"requiresCustomSQL": true
}
],
"workspaceSelection": {
"commonFields": [],
"datasets": [
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 200 Professional UK",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "E3487B36-735E-5C77-859B-D4C1CBC9C6E9",
"key": "ItemCode",
"type": "text"
},
{
"direction": "ascending",
"id": "5804FCDB-2765-50F4-823D-487355A62A2D",
"key": "WarehouseItemKey",
"type": "text"
}
],
"id": "5E6FF497-E789-522E-B10E-B6DCC680CF55",
"mappings": [
{
"commonFieldKey": "",
"key": "WarehouseItemKey",
"label": "Internal warehouse/item key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseItemKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "WarehouseKey",
"label": "Internal warehouse key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemKey",
"label": "Internal item key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemCode",
"label": "Product code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemName",
"label": "Product name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemDescription",
"label": "Product description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemDescription",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "GroupCode",
"label": "Product group code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "GroupCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "GroupName",
"label": "Product group description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "GroupName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemType",
"label": "Product type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemStatus",
"label": "Current product status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemStatus",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Traceability",
"label": "Traceability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Traceability",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Manufacturer",
"label": "Manufacturer",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Manufacturer",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PartNumber",
"label": "Manufacturer part number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PartNumber",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Barcode",
"label": "Barcode",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Barcode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "StockUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "BaseUnit",
"label": "Base unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BaseUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StockBaseMultiplier",
"label": "Stock/base unit multiplier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "StockBaseMultiplier",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "WarehouseName",
"label": "Warehouse",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseName",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "WarehouseDescription",
"label": "Warehouse description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseDescription",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "WarehouseSales",
"label": "Warehouse allows sales trading (not item eligibility)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseSales",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Confirmed",
"label": "Confirmed stock quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Confirmed",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Unconfirmed",
"label": "Unconfirmed stock quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Unconfirmed",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InStock",
"label": "In stock — confirmed + unconfirmed",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InStock",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "AllocatedSales",
"label": "Allocated to sales orders",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AllocatedSales",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AllocatedStock",
"label": "Allocated by Stock Control",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AllocatedStock",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AllocatedBOM",
"label": "Allocated to bills of materials",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AllocatedBOM",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Allocated",
"label": "Total allocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Allocated",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Unallocated",
"label": "Unallocated — not certified sellable stock",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Unallocated",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Reserved",
"label": "Reserved quantity (shown separately)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Reserved",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "OnPurchaseOrder",
"label": "Quantity on purchase orders",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OnPurchaseOrder",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ReorderLevel",
"label": "Reorder level",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ReorderLevel",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "MinimumLevel",
"label": "Minimum stock level",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "MinimumLevel",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MaximumLevel",
"label": "Maximum stock level",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "MaximumLevel",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastSale",
"label": "Last sale date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastSale",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastCount",
"label": "Last stock count date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastCount",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Warehouse stock",
"primaryKey": "WarehouseItemKey",
"queryParameters": [],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"ItemCode",
"ItemName",
"ItemDescription",
"GroupCode",
"GroupName",
"ItemType",
"ItemStatus",
"Traceability",
"Manufacturer",
"PartNumber",
"Barcode",
"StockUnit",
"BaseUnit",
"WarehouseName",
"WarehouseDescription",
"WarehouseSales"
],
"sourceID": "A946CAF9-7CEF-5438-B908-D303DD4B5E41",
"sqlQuery": "SELECT a.WarehouseItemID AS WarehouseItemKey, a.WarehouseID AS WarehouseKey, a.ItemID AS ItemKey, s.Code AS ItemCode, s.Name AS ItemName, s.Description AS ItemDescription, g.Code AS GroupCode, g.Description AS GroupName, CASE g.StockItemTypeID WHEN 0 THEN N'Stock' WHEN 1 THEN N'Miscellaneous' WHEN 2 THEN N'Service/Labour' WHEN 3 THEN N'Undefined' WHEN 4 THEN N'SOPItemDespatched' WHEN 5 THEN N'SOPItemNotDespatched' ELSE CASE WHEN g.StockItemTypeID IS NULL THEN N'Not recorded' ELSE CONCAT(N'Unknown native value: ',g.StockItemTypeID) END END AS ItemType, CASE s.StockItemStatusID WHEN 0 THEN N'Active' WHEN 1 THEN N'Inactive' ELSE CASE WHEN s.StockItemStatusID IS NULL THEN N'Not recorded' ELSE CONCAT(N'Unknown native value: ',s.StockItemStatusID) END END AS ItemStatus, CASE s.TraceableTypeID WHEN 0 THEN N'Non-traceable' WHEN 1 THEN N'Serial Numbered' WHEN 2 THEN N'Batch Numbered' ELSE CASE WHEN s.TraceableTypeID IS NULL THEN N'Not recorded' ELSE CONCAT(N'Unknown native value: ',s.TraceableTypeID) END END AS Traceability, s.Manufacturer AS Manufacturer, s.PartNumber AS PartNumber, s.Barcode AS Barcode, s.StockUnitName AS StockUnit, s.BaseUnitName AS BaseUnit, s.StockMultOfBaseUnit AS StockBaseMultiplier, w.Name AS WarehouseName, w.Description AS WarehouseDescription, CASE w.UseForSalesTrading WHEN 1 THEN N'Yes' WHEN 0 THEN N'No' ELSE N'Not recorded / unknown' END AS WarehouseSales, a.ConfirmedQtyInStock AS Confirmed, a.UnconfirmedQtyInStock AS Unconfirmed, (a.ConfirmedQtyInStock + a.UnconfirmedQtyInStock) AS InStock, a.QuantityAllocatedSOP AS AllocatedSales, a.QuantityAllocatedStock AS AllocatedStock, a.QuantityAllocatedBOM AS AllocatedBOM, (a.QuantityAllocatedSOP + a.QuantityAllocatedStock + a.QuantityAllocatedBOM) AS Allocated, ((a.ConfirmedQtyInStock + a.UnconfirmedQtyInStock) - (a.QuantityAllocatedSOP + a.QuantityAllocatedStock + a.QuantityAllocatedBOM)) AS Unallocated, a.QuantityReserved AS Reserved, a.QuantityOnPOPOrder AS OnPurchaseOrder, a.ReorderLevel AS ReorderLevel, a.MinimumLevel AS MinimumLevel, a.MaximumLevel AS MaximumLevel, a.DateOfLastSale AS LastSale, a.DateOfLastStockCount AS LastCount FROM dbo.WarehouseItem a INNER JOIN dbo.StockItem s ON s.ItemID=a.ItemID INNER JOIN dbo.Warehouse w ON w.WarehouseID=a.WarehouseID LEFT JOIN dbo.ProductGroup g ON g.ProductGroupID=s.ProductGroupID WHERE a.WarehouseItemID IS NOT NULL",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 200 Professional UK",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "6B674C6B-8932-5455-85F2-969B6802BC6D",
"key": "BinName",
"type": "text"
},
{
"direction": "ascending",
"id": "CE8B032D-AC6F-5BF3-A54F-BC412C6E9216",
"key": "BinKey",
"type": "text"
}
],
"id": "DC9EBF58-337F-5ED6-9879-071865915A94",
"mappings": [
{
"commonFieldKey": "",
"key": "WarehouseItemKey",
"label": "Internal warehouse/item key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseItemKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "WarehouseKey",
"label": "Internal warehouse key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemKey",
"label": "Internal item key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BinKey",
"label": "Internal bin/item key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BinKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BinName",
"label": "Bin location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BinName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemCode",
"label": "Product code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ItemName",
"label": "Product name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "StockUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "WarehouseName",
"label": "Warehouse",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseName",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "UnspecifiedBin",
"label": "Bin classification",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "UnspecifiedBin",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Confirmed",
"label": "Confirmed stock quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Confirmed",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Unconfirmed",
"label": "Unconfirmed stock quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Unconfirmed",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InStock",
"label": "In stock — confirmed + unconfirmed",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InStock",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "AllocatedSales",
"label": "Allocated to sales orders",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AllocatedSales",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AllocatedStock",
"label": "Allocated by Stock Control",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AllocatedStock",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AllocatedBOM",
"label": "Allocated to bills of materials",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AllocatedBOM",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Allocated",
"label": "Total allocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Allocated",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Unallocated",
"label": "Unallocated — not certified sellable stock",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Unallocated",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Reserved",
"label": "Reserved quantity (shown separately)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Reserved",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "LastCount",
"label": "Last stock count date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastCount",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Stock bins",
"primaryKey": "BinKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "WarehouseItemKey",
"id": "AB9DBFB6-DA0B-5FC6-BED0-DD5C6C4E6729",
"name": "warehouse_item",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "WarehouseKey",
"id": "7F6E4396-F573-52C1-BA44-4A1C230A1B7C",
"name": "warehouse",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ItemKey",
"id": "0D6ADE2A-D023-5070-B99A-B9093F19A366",
"name": "item",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"BinName",
"ItemCode",
"ItemName",
"StockUnit",
"WarehouseName",
"UnspecifiedBin"
],
"sourceID": "A946CAF9-7CEF-5438-B908-D303DD4B5E41",
"sqlQuery": "SELECT a.WarehouseItemID AS WarehouseItemKey, a.WarehouseID AS WarehouseKey, a.ItemID AS ItemKey, b.BinItemID AS BinKey, b.BinName AS BinName, s.Code AS ItemCode, s.Name AS ItemName, s.StockUnitName AS StockUnit, w.Name AS WarehouseName, CASE b.UnspecifiedBin WHEN 1 THEN N'Unspecified bin' WHEN 0 THEN N'Specified bin' ELSE N'Bin setting unknown' END AS UnspecifiedBin, b.ConfirmedQtyInStock AS Confirmed, b.UnconfirmedQtyInStock AS Unconfirmed, (b.ConfirmedQtyInStock + b.UnconfirmedQtyInStock) AS InStock, b.QuantityAllocatedSOP AS AllocatedSales, b.QuantityAllocatedStock AS AllocatedStock, b.QuantityAllocatedBOM AS AllocatedBOM, (b.QuantityAllocatedSOP + b.QuantityAllocatedStock + b.QuantityAllocatedBOM) AS Allocated, ((b.ConfirmedQtyInStock + b.UnconfirmedQtyInStock) - (b.QuantityAllocatedSOP + b.QuantityAllocatedStock + b.QuantityAllocatedBOM)) AS Unallocated, b.QuantityReserved AS Reserved, b.DateOfLastStockCount AS LastCount FROM dbo.BinItem b INNER JOIN dbo.WarehouseItem a ON a.WarehouseItemID=b.WarehouseItemID AND a.ItemID=b.ItemID INNER JOIN dbo.StockItem s ON s.ItemID=a.ItemID INNER JOIN dbo.Warehouse w ON w.WarehouseID=a.WarehouseID WHERE a.WarehouseItemID=:warehouse_item AND a.WarehouseID=:warehouse AND a.ItemID=:item AND b.BinItemID IS NOT NULL",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 200 Professional UK",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "BF5377F6-03BF-5310-84F6-E490BBC21FB7",
"key": "SourceReference",
"type": "text"
},
{
"direction": "ascending",
"id": "3A359890-D23E-53B8-B412-0A891F86A176",
"key": "MovementKey",
"type": "text"
}
],
"id": "0739D71D-A3B1-55B0-8C04-53E199414D40",
"mappings": [
{
"commonFieldKey": "",
"key": "WarehouseItemKey",
"label": "Internal warehouse/item key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseItemKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "WarehouseKey",
"label": "Internal warehouse key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemKey",
"label": "Internal item key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BinKey",
"label": "Internal bin/item key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BinKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BinName",
"label": "Bin location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BinName",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ItemCode",
"label": "Product code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ItemName",
"label": "Product name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "StockUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "WarehouseName",
"label": "Warehouse",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SourceArea",
"label": "Source category",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SourceArea",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MovementKey",
"label": "Internal movement balance key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "MovementKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SourceReference",
"label": "Source reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SourceReference",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SourceName",
"label": "Source name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SourceName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OpeningQuantity",
"label": "Opening movement-balance quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OpeningQuantity",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "IssuedQuantity",
"label": "Issued from this balance",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "IssuedQuantity",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "RemainingQuantity",
"label": "Remaining balance — opening minus issued",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RemainingQuantity",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
}
],
"maxRows": 2000,
"name": "Movement balances",
"primaryKey": "MovementKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "WarehouseItemKey",
"id": "AB9DBFB6-DA0B-5FC6-BED0-DD5C6C4E6729",
"name": "warehouse_item",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "WarehouseKey",
"id": "7F6E4396-F573-52C1-BA44-4A1C230A1B7C",
"name": "warehouse",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ItemKey",
"id": "0D6ADE2A-D023-5070-B99A-B9093F19A366",
"name": "item",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "BinKey",
"id": "73AAD224-67BC-55E9-9B92-FE965695BA1B",
"name": "bin",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"BinName",
"ItemCode",
"ItemName",
"StockUnit",
"WarehouseName",
"SourceArea",
"SourceReference",
"SourceName"
],
"sourceID": "A946CAF9-7CEF-5438-B908-D303DD4B5E41",
"sqlQuery": "SELECT a.WarehouseItemID AS WarehouseItemKey, a.WarehouseID AS WarehouseKey, a.ItemID AS ItemKey, b.BinItemID AS BinKey, b.BinName AS BinName, s.Code AS ItemCode, s.Name AS ItemName, s.StockUnitName AS StockUnit, w.Name AS WarehouseName, CASE v.SourceAreaTypeID WHEN 0 THEN N'Supplier' WHEN 1 THEN N'Customer' WHEN 2 THEN N'Internal' WHEN 3 THEN N'Write Off' WHEN 4 THEN N'Opening Balance' WHEN 5 THEN N'BOM' ELSE CASE WHEN v.SourceAreaTypeID IS NULL THEN N'Not recorded' ELSE CONCAT(N'Unknown native value: ',v.SourceAreaTypeID) END END AS SourceArea, v.MovementID AS MovementKey, v.SourceAreaReference AS SourceReference, v.SourceAreaName AS SourceName, v.OpeningStockLevel AS OpeningQuantity, v.StockLevelIssued AS IssuedQuantity, (v.OpeningStockLevel - v.StockLevelIssued) AS RemainingQuantity FROM dbo.MovementBalance v INNER JOIN dbo.BinItem b ON b.BinItemID=v.BinItemID AND b.ItemID=v.ItemID INNER JOIN dbo.WarehouseItem a ON a.WarehouseItemID=b.WarehouseItemID AND a.ItemID=b.ItemID INNER JOIN dbo.StockItem s ON s.ItemID=a.ItemID INNER JOIN dbo.Warehouse w ON w.WarehouseID=a.WarehouseID WHERE a.WarehouseItemID=:warehouse_item AND a.WarehouseID=:warehouse AND a.ItemID=:item AND b.BinItemID=:bin AND v.WarehouseID=a.WarehouseID AND v.MovementID IS NOT NULL",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 200 Professional UK",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "descending",
"id": "40BA2E5B-F167-5A36-AE4D-362F809A344D",
"key": "AllocationDate",
"type": "date"
},
{
"direction": "ascending",
"id": "05298CA0-52AE-5059-A69C-ACE2FC4F7EFD",
"key": "AllocationKey",
"type": "text"
}
],
"id": "BF33BCE5-3116-5596-8D64-D7B50574EF12",
"mappings": [
{
"commonFieldKey": "",
"key": "WarehouseItemKey",
"label": "Internal warehouse/item key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseItemKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "WarehouseKey",
"label": "Internal warehouse key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemKey",
"label": "Internal item key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BinKey",
"label": "Internal bin/item key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BinKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BinName",
"label": "Bin location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BinName",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ItemCode",
"label": "Product code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ItemName",
"label": "Product name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "StockUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "WarehouseName",
"label": "Warehouse",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SourceArea",
"label": "Source category",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SourceArea",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AllocationKey",
"label": "Internal allocation key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AllocationKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RecipientReference",
"label": "Recipient reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RecipientReference",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RecipientName",
"label": "Recipient name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RecipientName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AllocatedQuantity",
"label": "Allocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AllocatedQuantity",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "AllocationDate",
"label": "Allocation date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AllocationDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
}
],
"maxRows": 2000,
"name": "Stock allocations",
"primaryKey": "AllocationKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "WarehouseItemKey",
"id": "AB9DBFB6-DA0B-5FC6-BED0-DD5C6C4E6729",
"name": "warehouse_item",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "WarehouseKey",
"id": "7F6E4396-F573-52C1-BA44-4A1C230A1B7C",
"name": "warehouse",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ItemKey",
"id": "0D6ADE2A-D023-5070-B99A-B9093F19A366",
"name": "item",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "BinKey",
"id": "73AAD224-67BC-55E9-9B92-FE965695BA1B",
"name": "bin",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"BinName",
"ItemCode",
"ItemName",
"StockUnit",
"WarehouseName",
"SourceArea",
"RecipientReference",
"RecipientName"
],
"sourceID": "A946CAF9-7CEF-5438-B908-D303DD4B5E41",
"sqlQuery": "SELECT a.WarehouseItemID AS WarehouseItemKey, a.WarehouseID AS WarehouseKey, a.ItemID AS ItemKey, b.BinItemID AS BinKey, b.BinName AS BinName, s.Code AS ItemCode, s.Name AS ItemName, s.StockUnitName AS StockUnit, w.Name AS WarehouseName, CASE v.SourceAreaTypeID WHEN 0 THEN N'Supplier' WHEN 1 THEN N'Customer' WHEN 2 THEN N'Internal' WHEN 3 THEN N'Write Off' WHEN 4 THEN N'Opening Balance' WHEN 5 THEN N'BOM' ELSE CASE WHEN v.SourceAreaTypeID IS NULL THEN N'Not recorded' ELSE CONCAT(N'Unknown native value: ',v.SourceAreaTypeID) END END AS SourceArea, v.AllocationID AS AllocationKey, v.RecipientReference AS RecipientReference, v.RecipientName AS RecipientName, v.AllocatedQuantity AS AllocatedQuantity, v.AllocationDate AS AllocationDate FROM dbo.AllocationBalance v INNER JOIN dbo.BinItem b ON b.BinItemID=v.BinItemID AND b.ItemID=v.ItemID INNER JOIN dbo.WarehouseItem a ON a.WarehouseItemID=b.WarehouseItemID AND a.ItemID=b.ItemID INNER JOIN dbo.StockItem s ON s.ItemID=a.ItemID INNER JOIN dbo.Warehouse w ON w.WarehouseID=a.WarehouseID WHERE a.WarehouseItemID=:warehouse_item AND a.WarehouseID=:warehouse AND a.ItemID=:item AND b.BinItemID=:bin AND v.WarehouseID=a.WarehouseID AND v.AllocationID IS NOT NULL",
"tableName": ""
}
],
"pages": [
{
"actions": [
{
"id": "82BA57BA-5BA6-5A65-8FCF-8876E78B118F",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "0D1EE008-092B-58CC-B824-7DAFB6697BC3",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "DC9EBF58-337F-5ED6-9879-071865915A94",
"title": "Bins",
"urlKey": ""
}
],
"badgeKey": "ItemStatus",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "WarehouseName",
"label": "Warehouse",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "InStock",
"label": "Stock quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "Allocated",
"label": "Allocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "Unallocated",
"label": "Unallocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "Reserved",
"label": "Reserved quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "OnPurchaseOrder",
"label": "On purchase orders",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ReorderLevel",
"label": "Reorder level",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "5E6FF497-E789-522E-B10E-B6DCC680CF55",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "ItemCode",
"label": "Product code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "ItemName",
"label": "Product name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "ItemDescription",
"label": "Product description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "GroupCode",
"label": "Product group code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "GroupName",
"label": "Product group description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "ItemType",
"label": "Product type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "ItemStatus",
"label": "Current product status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "Traceability",
"label": "Traceability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "Manufacturer",
"label": "Manufacturer",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "PartNumber",
"label": "Manufacturer part number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "Barcode",
"label": "Barcode",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "BaseUnit",
"label": "Base unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "StockBaseMultiplier",
"label": "Stock/base unit multiplier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "WarehouseName",
"label": "Warehouse",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "WarehouseDescription",
"label": "Warehouse description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "WarehouseSales",
"label": "Warehouse allows sales trading (not item eligibility)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "Confirmed",
"label": "Confirmed stock quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "Unconfirmed",
"label": "Unconfirmed stock quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "InStock",
"label": "In stock — confirmed + unconfirmed",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "AllocatedSales",
"label": "Allocated to sales orders",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "AllocatedStock",
"label": "Allocated by Stock Control",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "AllocatedBOM",
"label": "Allocated to bills of materials",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "Allocated",
"label": "Total allocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "Unallocated",
"label": "Unallocated — not certified sellable stock",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "Reserved",
"label": "Reserved quantity (shown separately)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "OnPurchaseOrder",
"label": "Quantity on purchase orders",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "ReorderLevel",
"label": "Reorder level",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "MinimumLevel",
"label": "Minimum stock level",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "MaximumLevel",
"label": "Maximum stock level",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "LastSale",
"label": "Last sale date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "LastCount",
"label": "Last stock count date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "BC6EF081-657A-56A9-A378-07A7FF413021",
"openFilters": [
{
"id": "78B34225-70F9-5575-A1AA-0F652A0140B0",
"includeAllOption": false,
"key": "WarehouseName",
"title": "Choose a warehouse",
"type": "text"
}
],
"pageSize": 100,
"requiresOpeningFilterSelection": true,
"showOnHome": true,
"sortRules": [
{
"direction": "ascending",
"id": "E3487B36-735E-5C77-859B-D4C1CBC9C6E9",
"key": "ItemCode",
"type": "text"
},
{
"direction": "ascending",
"id": "5804FCDB-2765-50F4-823D-487355A62A2D",
"key": "WarehouseItemKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "ItemCode",
"systemImage": "shippingbox",
"title": "Warehouse stock",
"titleKey": "ItemName"
},
{
"actions": [
{
"id": "DB4A782B-6AC2-5931-9459-012873B96945",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "9723F0E6-5441-523D-A901-3E0889670338",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "0739D71D-A3B1-55B0-8C04-53E199414D40",
"title": "Movement balances",
"urlKey": ""
},
{
"id": "91EFA5C4-1E52-54B7-BA1B-BF5761DE577E",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "AA8FDC77-5FA1-55E5-8AE0-28692C459FAC",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "BF33BCE5-3116-5596-8D64-D7B50574EF12",
"title": "Allocations",
"urlKey": ""
}
],
"badgeKey": "UnspecifiedBin",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ItemCode",
"label": "Product code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "WarehouseName",
"label": "Warehouse",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "InStock",
"label": "Stock quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "Allocated",
"label": "Allocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "Unallocated",
"label": "Unallocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "Reserved",
"label": "Reserved quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "DC9EBF58-337F-5ED6-9879-071865915A94",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "BinName",
"label": "Bin location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "ItemCode",
"label": "Product code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "ItemName",
"label": "Product name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "WarehouseName",
"label": "Warehouse",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "UnspecifiedBin",
"label": "Bin classification",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "Confirmed",
"label": "Confirmed stock quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "Unconfirmed",
"label": "Unconfirmed stock quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "InStock",
"label": "In stock — confirmed + unconfirmed",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "AllocatedSales",
"label": "Allocated to sales orders",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "AllocatedStock",
"label": "Allocated by Stock Control",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "AllocatedBOM",
"label": "Allocated to bills of materials",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "Allocated",
"label": "Total allocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "Unallocated",
"label": "Unallocated — not certified sellable stock",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "Reserved",
"label": "Reserved quantity (shown separately)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "LastCount",
"label": "Last stock count date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "99CC9CC8-CEF7-5704-AACE-912078C2FAEA",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "6B674C6B-8932-5455-85F2-969B6802BC6D",
"key": "BinName",
"type": "text"
},
{
"direction": "ascending",
"id": "CE8B032D-AC6F-5BF3-A54F-BC412C6E9216",
"key": "BinKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "ItemName",
"systemImage": "shippingbox",
"title": "Stock bins",
"titleKey": "BinName"
},
{
"actions": [],
"badgeKey": "SourceArea",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "BinName",
"label": "Bin location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ItemCode",
"label": "Product code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "OpeningQuantity",
"label": "Opening quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "IssuedQuantity",
"label": "Quantity issued",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "RemainingQuantity",
"label": "Remaining quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "0739D71D-A3B1-55B0-8C04-53E199414D40",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "BinName",
"label": "Bin location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "ItemCode",
"label": "Product code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "ItemName",
"label": "Product name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "WarehouseName",
"label": "Warehouse",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "SourceArea",
"label": "Source category",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "SourceReference",
"label": "Source reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "SourceName",
"label": "Source name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "OpeningQuantity",
"label": "Opening movement-balance quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "IssuedQuantity",
"label": "Issued from this balance",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "RemainingQuantity",
"label": "Remaining balance — opening minus issued",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "167A88C4-93B4-5298-9855-32F0CDC628C6",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "BF5377F6-03BF-5310-84F6-E490BBC21FB7",
"key": "SourceReference",
"type": "text"
},
{
"direction": "ascending",
"id": "3A359890-D23E-53B8-B412-0A891F86A176",
"key": "MovementKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "SourceName",
"systemImage": "shippingbox",
"title": "Movement balances",
"titleKey": "SourceReference"
},
{
"actions": [],
"badgeKey": "SourceArea",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "BinName",
"label": "Bin location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ItemCode",
"label": "Product code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "AllocatedQuantity",
"label": "Allocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "AllocationDate",
"label": "Allocation date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "BF33BCE5-3116-5596-8D64-D7B50574EF12",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "BinName",
"label": "Bin location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "ItemCode",
"label": "Product code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "ItemName",
"label": "Product name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "WarehouseName",
"label": "Warehouse",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "SourceArea",
"label": "Source category",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "RecipientReference",
"label": "Recipient reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "RecipientName",
"label": "Recipient name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current quantities — in the displayed stock unit",
"detailRole": "information",
"isVisible": true,
"key": "AllocatedQuantity",
"label": "Allocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Current stock record",
"detailRole": "information",
"isVisible": true,
"key": "AllocationDate",
"label": "Allocation date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "33DD439F-0B0D-567A-8DE7-50D59C9CA408",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "descending",
"id": "40BA2E5B-F167-5A36-AE4D-362F809A344D",
"key": "AllocationDate",
"type": "date"
},
{
"direction": "ascending",
"id": "05298CA0-52AE-5059-A69C-ACE2FC4F7EFD",
"key": "AllocationKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "RecipientName",
"systemImage": "shippingbox",
"title": "Stock allocations",
"titleKey": "RecipientReference"
}
],
"relations": [
{
"childDatasetID": "DC9EBF58-337F-5ED6-9879-071865915A94",
"childKey": "WarehouseItemKey",
"id": "0D1EE008-092B-58CC-B824-7DAFB6697BC3",
"name": "Bins",
"parentDatasetID": "5E6FF497-E789-522E-B10E-B6DCC680CF55",
"parentKey": "WarehouseItemKey"
},
{
"childDatasetID": "0739D71D-A3B1-55B0-8C04-53E199414D40",
"childKey": "BinKey",
"id": "9723F0E6-5441-523D-A901-3E0889670338",
"name": "Movement balances",
"parentDatasetID": "DC9EBF58-337F-5ED6-9879-071865915A94",
"parentKey": "BinKey"
},
{
"childDatasetID": "BF33BCE5-3116-5596-8D64-D7B50574EF12",
"childKey": "BinKey",
"id": "AA8FDC77-5FA1-55E5-8AE0-28692C459FAC",
"name": "Allocations",
"parentDatasetID": "DC9EBF58-337F-5ED6-9879-071865915A94",
"parentKey": "BinKey"
}
],
"widgets": []
}
},
"format": "cifru-configuration-package",
"formatVersion": 1,
"manifest": {
"applicationName": "Sage 200 Professional UK",
"configurationLanguages": [
"en"
],
"countries": [
"GB"
],
"createdAt": "2026-10-10T00:00:00Z",
"description": "DOCUMENTARY AND SYNTHETIC VALIDATION ONLY — not tested on a real Sage 200 installation. For warehouse staff, purchasing, sales and managers: choose a warehouse when opening the screen. Inspect each product with its group, type, current status, manufacturer, part number, barcode and stock/base units. See confirmed and unconfirmed stock, allocations to sales orders, Stock Control and bills of materials, reservations, quantity on purchase orders, minimum/maximum/reorder levels and last sale/count dates. Open the product’s bins, then the bin’s individual movement balances or allocations with their source or recipient, quantities and allocation dates. Zero, negative, inactive and unfamiliar native values are not silently hidden.\n\nWhy Cifru? Adapt configurations to the way you work. Choose the fields, filters and details you need, and bring information to your phone that may not be available in your business software’s own mobile app. Available options depend on the data exposed by your authorised source and your Cifru plan.\n\nPro: one SQL Server source, four lists, full Details and three on-demand related buttons; custom read-only SELECT. Warehouse selection uses the native opening filter; source reads apply that selection after reading the available choices. At most 2,000 rows per list/read; no periodic refresh. Lists and related reads may be incomplete at this limit. Local search/filters affect loaded rows only. Row limits do not guarantee an inexpensive query: review execution plans and timeouts with the database administrator. Required parent keys are carried as text and rechecked for warehouse, product, warehouse-item and bin before each child read. Navigation is not a permission boundary.\n\nQuantity semantics: in stock = confirmed + unconfirmed; allocated = sales + Stock Control + BOM; unallocated = in stock − allocated, following the selected legacy guide. Reservations are shown separately, not deducted a second time. Unallocated quantity is NOT a guarantee that goods are sellable, pickable or available to promise. Warehouse sales-trading permission is only one condition; product rules, traceability, returns and other restrictions still apply. Each quantity uses that product’s stock unit. No sum across unlike units, warehouses or products is presented. Missing operands remain missing, not invented zeros. Movement balances are current stock components (opening minus issued), not a full dated movement ledger. Allocations are recorded bin allocations, not posted invoices or despatches. No financial valuation, currency, pricing, historical stock or reconciliation guarantee is claimed.\n\nUnofficial, not affiliated with Sage. Physical identifiers come from the Sage 200 2015 database guide (November 2014), not a complete DDL or proof of compatibility with current versions. The guide contradicts itself on the Unconfirmed Return movement-state code; this template intentionally does not translate/filter that state. Modern Professional help explains usage only, not an unchanged physical schema. Requires the selected dbo objects in one authorised Sage 200 Professional UK company SQL Server database; verify installed schema, types, uniqueness, edition and read compatibility before use. Not Sage 200 Standard, Sage 200 Evolution or Sage 300. Direct SQL does not inherit Sage user roles or company permissions; use a separate least-privilege authorised SQL reader. No connection addresses, credentials or business rows are included. Native DEMO captures use wholly fictional local data, not a real SQL Server or Sage compatibility test.\n\nPrimary physical guide: https://desktophelp.sage.co.uk/sage200/PDF/2015/Understanding%20the%20Sage%20200%202015%20Database.pdf\nWarehouse/bin usage: https://desktophelp.sage.co.uk/sage200/professional/Content/SC/WarehouseList.htm",
"licenseCode": "Cifru-Community-1.0",
"minimumCifruVersion": "1.1.0",
"minimumPlan": "pro",
"packageID": "8C5245E5-A430-5912-B055-5E1A8355E054",
"rootButtonCount": 1,
"summary": "Choose a warehouse; inspect products, stock components, bins, movement balances and allocations on demand.",
"tags": [
"Sage 200 Professional UK",
"SQL Server",
"Inventory",
"Warehouse stock",
"Stock allocations",
"Purchasing",
"Pro"
],
"title": "Warehouse stock, bins and allocations — Pro"
}
}