P4 Software
/
logisticaHQ-en
When an operator on the Handheld (the gun) receives stock, receives a return, or moves stock directly, the gun asks LogisticaHQ for a short list of Bins where the stock could go. The list comes from a SQL query stored in the company's Configuration. There are three of them:
| Setting (as listed in Configuration) | Where the gun uses it |
|---|---|
Advanced > Receiving > Putaway Query (Advanced_Receiving_Handheld_PutawayQuery) |
When a purchase order line is received. |
Advanced > Returns > Putaway Query (Advanced_Returns_Handheld_PutawayQuery) |
When a returned (RMA) line is received. |
Advanced > Direct move > Recommended Bins (Advanced_DirectMove_Handheld_RecommendedBins) |
When an operator moves stock directly and the gun recommends Bins. |
Until this release those three settings were plain text boxes. They now open in the same SQL workbench the Query builder uses, with suggestions for tables, columns and parameters, ten ready-made examples, a clear sign of whether the shipped default is in use, and a Test with a product panel that runs the query exactly as the gun would and shows the Bins in the order they would be offered. A query that fails its test cannot be saved.
What you see: the setting's label and description on the left, the status line at the right of it, the editor with its toolbar below, the validity line under the editor and, under that, the Test with a product panel.
Setup > Configuration, then open Advanced in the left list and choose Receiving, Returns or Direct move. You can also press Ctrl+K and type "putaway" or "recommended Bins", or use Find a setting at the top of the page; the result opens the exact row.
All other multi-line settings in Configuration keep their ordinary text box. Only these three names use the editor.
Knowing this makes every message in the editor easy to read.
| Parameter | Receiving | Returns | Direct move |
|---|---|---|---|
@WarehouseId |
The Warehouse the operator is Receiving in. | The Warehouse the operator is Receiving the return in. | The Warehouse the operator is moving in. |
@ProductId |
The product on the purchase order line. | The product on the return line. | The product being moved. |
@PacksizeId |
The packsize scanned; empty for an each. | The packsize scanned; empty for an each. | The packsize being moved; empty for an each. |
@Quantity |
How many units are being put away. | How many units are being put away. | How many units are being moved. |
BinCode @ ZoneCode, for example A-01-03 @ RECEIVE-A, which is what every example below Returns. Other columns are ignored.order by in your query; without one the database may return rows in any order.What you see: the status line reads "Edited ยท differs from the shipped default" and the Restore the shipped default button sits right next to it.
The editor is the same one the Query builder uses.
Bins.) to see that table's columns. Each suggestion shows the column's data type and its table, for example nvarchar ยท Bins.What you see: after typing Bins.Al a small list opens under the cursor with the columns of Bins that start with those letters, each with its data type and table.
Under the editor a coloured pill tells you at once whether the text can work on the gun. It is computed in your browser as you type; the server checks again with its own, stricter read-only check, which remains the authority.
| Pill | Meaning |
|---|---|
| Green: Valid: one read-only SELECT, N parameters used (or "1 parameter used", "no parameters used") | One SELECT (or WITH ... SELECT), nothing that changes data, only the four known parameters. N is how many of the four the text uses. |
| Grey: Empty: the shipped default query is used | The text is empty; the gun will run the shipped default. |
| Red: Only a SELECT query can be saved here. Start with SELECT. | The text starts with something else. |
| Red: Nothing can be added, changed or deleted here. Remove INSERT. (or UPDATE, DELETE, MERGE, DROP, ALTER, CREATE, TRUNCATE, EXEC, EXECUTE, DECLARE, GRANT, REVOKE, WAITFOR, BACKUP, INTO) | One of those words appears in the query outside quotes and comments. |
| Red: Only one query is allowed. Remove everything after the first semicolon. | A second statement follows a semicolon. A trailing semicolon alone is fine. |
| Red: The query uses @Foo, which the gun does not give. Use only @WarehouseId, @ProductId, @PacksizeId, @Quantity. | A parameter other than the four. @@ROWCOUNT and similar are not parameters and are ignored. |
Words inside quotes ('...'), comments (-- ... and /* ... */) and [bracketed names] are ignored by this check.
The caption at the right of the pill says Changes apply when you press Save at the bottom of the page. When the text differs from what is saved, is not empty, has no problem, and has not been tested in this visit, it says instead Not tested yet with this text. Press Run test below before you save. That is a nudge, not a block.
Press Examples (10). The menu opens with a short introduction, three groups and ten examples, then a Closing note. Each item shows a title, a description and, for the first one, a shipped default tag. All three settings offer the same ten examples, because they use only the four parameters the gun always passes. Their SQL is listed in full at the end of this page.
What you see: the Examples menu open, with the introduction at the top, the three group headings, the ten items (title and description) and the Closing note; the first item carries the "shipped default" tag and the item you loaded carries the "loaded" tag.
After you pick an example, press Restore the shipped default or press Clear, the status area shows an amber pill Example loaded: <title> (or Restored the shipped default, or Cleared the query) with an Undo button.
What you see: where the status line normally is, an amber pill reading "Example loaded: Top up where it already is" with an Undo button next to it.
This panel runs the query above exactly as the gun does, on the company's real stock data, so you can see which Bins a product would be offered. Nothing is saved or moved: the test is read-only (see "Protections").
| Control | What it does |
|---|---|
| Warehouse | The Warehouse passed as @WarehouseId. The list holds the active Warehouses by code; the first is preselected. |
| Product | A search box. Type at least two characters of the SKU or the description and wait a moment: up to eight matching active Products appear. Use Up/Down and Enter, or click, to choose; Esc closes the list. The chosen product shows as "SKU ยท Description" and is passed as @ProductId. Typing again clears the choice. If nothing matches, the list says "No product matches this search." Discontinued Products are not offered; a 3PL user only finds their own client's Products. |
| Packsize | Each (empty, passed as @PacksizeId = NULL) or one of the chosen product's Packsizes, shown as "Name (N each)". Disabled until a product is chosen and reset to Each when the product changes. |
| Quantity | The number of units passed as @Quantity. It must be more than zero. Default 1. |
| Run test | Runs the test. Disabled until a Warehouse and a product are chosen, the quantity is more than zero and the query has no problem; while it runs it reads "Running...". Under the fields a note says "Choose a product to run the test." until a product is chosen. |
If the editor is empty, the test runs the shipped default, because that is what the gun would run.
Administrators only. If the server refuses the first request the editor makes (the shipped defaults, which only an Administrator may read), the controls are hidden and the panel says Only administrators can test these queries. instead; Run test then stays off and a test can never fail for that reason.
What you see: a light-blue panel titled "Test with a product" with the Warehouse list, the Product search box, the Packsize list, the Quantity box and the Run test button in one row.
Invalid column name 'X'., and below it "A query that fails here cannot be saved." Two kinds of failure use a fixed sentence instead of the database's text, so no server, database or login name is ever shown: when the read-only database cannot be reached the box reads "The read-only database could not be reached. Try again in a moment.", and for any other problem that is not a SQL error it reads "The query could not be run. Check that it Returns one text column and uses only the four parameters." A query the server's read-only check refuses is reported with that check's own sentence (in English), and a company without a read-only database reads "This company has no read-only database to test on."What you see: a line such as "3 Bins in 41 ms ยท in the order the gun would offer them" and, under it, one rounded chip per bin with its position number; the first chip is highlighted and marked "offered first".
What you see: after a failed test a red box with the message and, under it, the grey note "A query that fails here cannot be saved."; the validity line and the Save button show that Save is off.
The page's Save and Discard buttons at the bottom work as for every other setting. Save sends the query trimmed of spaces at both ends.
The editor and the test cannot change data. Three independent locks apply:
In addition, the test has a 5 second limit and a 50 row cap, and the server does not write the query text or its rows to any log.
Why Administrators only. The query is run for real, on real stock data, and a stored query decides what every operator is offered, so editing and testing it is limited to Administrators. This is also why the test reads the live data: only the live data tells you what the gun will really do.
order by.Every example starts with a comment line listing the parameters, uses only those four parameters, and Returns BinCode @ ZoneCode as its first column. They are the same for Receiving, Returns and Direct move. Several of them rest on columns that stand in for a business idea (the bin sequence number as walking order, the maximum and minimum quantity of a bin as capacity, "allow several pallets per bin" as the mark of a pallet zone, the expiry date of the stock): run each one in the Test with a product panel with a real product before relying on it.
1. Empty bins in product Zones, by category and client (shipped default)
Bins with nothing in them, in Zones that store by product, whose allowed categories and client match the product. This is the shipped default.
--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
Bins.BinCode+' @ '+Zones.ZoneCode
from
Bins
join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
join Products on Products.Id = @ProductId
left join InventoryLocations on Bins.Id = InventoryLocations.BinId
where
InventoryLocations.Id is null
and Zones.ProductHandling = 'ByProduct'
and (coalesce(Bins.AllowedCategories, Zones.AllowedCategories) is null or Products.Category in (select lower(ltrim(rtrim(value))) from string_split(coalesce(Bins.AllowedCategories, Zones.AllowedCategories), ',')))
and (coalesce(Bins.ClientId, Zones.ClientId) is null or coalesce(Bins.ClientId, Zones.ClientId) = Products.ClientId)
order by
coalesce(Bins.ClientId, Zones.ClientId) desc,
coalesce(Bins.AllowedCategories, Zones.AllowedCategories) desc,
Zones.ZoneCode,
Bins.BinCode
2. Empty bins in walking order
Empty bins in Zones that store by product, ordered by zone and then by the bin's sequence number, so the first offer is the next one along the aisle. The sequence number is stored as text, so "A-10" sorts before "A-2"; use zero-padded numbers such as "A-02" if you rely on this.
--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
Bins.BinCode + ' @ ' + Zones.ZoneCode
from
Bins
join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
InventoryLocations.Id is null
and Zones.ProductHandling = 'ByProduct'
order by
Zones.ZoneCode,
Bins.SequenceNumber,
Bins.BinCode
3. Any empty bin in the Warehouse
The simplest fallback: every empty bin that is not in quarantine, by zone and bin code, no category or client rules.
--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
Bins.BinCode + ' @ ' + Zones.ZoneCode
from
Bins
join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
InventoryLocations.Id is null
and Zones.IsQuarantine = 0
and coalesce(Bins.IsQuarantine, 0) = 0
order by
Zones.ZoneCode,
Bins.BinCode
4. Top up where it already is
Bins already holding this product come first, then Empty bins in the same kind of zone, so stock stays together.
--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
Bins.BinCode + ' @ ' + Zones.ZoneCode
from
Bins
join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
Zones.ProductHandling = 'ByProduct'
and (InventoryLocations.Id is null or InventoryLocations.ProductId = @ProductId)
group by
Bins.BinCode, Zones.ZoneCode
order by
max(case when InventoryLocations.ProductId = @ProductId then 1 else 0 end) desc,
Zones.ZoneCode,
Bins.BinCode
5. Pick Bins of this product, lowest first
The Bins dedicated to this product, those under their minimum quantity first. Returns nothing when the product has no dedicated bin.
--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
Bins.BinCode + ' @ ' + Zones.ZoneCode
from
Bins
join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
Bins.DedicatedProductId = @ProductId
group by
Bins.BinCode, Zones.ZoneCode, Bins.MinQuantity
order by
case when coalesce(sum(InventoryLocations.Quantity), 0) < coalesce(Bins.MinQuantity, 0) then 0 else 1 end,
Zones.ZoneCode,
Bins.BinCode
6. Bins with room left for this quantity
Bins that have a maximum quantity and still have room for this many units, fullest first, so partly filled Bins get finished before new ones open.
Unit note: stock in the Bins is counted in eaches, so the query turns @Quantity into eaches by multiplying it by the each count of @PacksizeId (1 when the packsize is empty). Putting away 2 cases of 12 therefore compares 24 eaches, not 2.
--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
Bins.BinCode + ' @ ' + Zones.ZoneCode
from
Bins
join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
Bins.MaxQuantity is not null
group by
Bins.BinCode, Zones.ZoneCode, Bins.MaxQuantity
having
Bins.MaxQuantity - coalesce(sum(InventoryLocations.Quantity), 0) >= (@Quantity * coalesce((select ProductPacksizes.EachCount from ProductPacksizes where ProductPacksizes.Id = @PacksizeId), 1))
order by
Bins.MaxQuantity - coalesce(sum(InventoryLocations.Quantity), 0),
Zones.ZoneCode,
Bins.BinCode
7. Expiry Products: beside the oldest stock
For Products that track expiry: Bins already holding this product, the one with the earliest expiry first, so picking can stay first-expiry-first-out. Returns nothing for other Products.
--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
Bins.BinCode + ' @ ' + Zones.ZoneCode
from
Bins
join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
join Products on Products.Id = @ProductId
join InventoryLocations on InventoryLocations.BinId = Bins.Id and InventoryLocations.ProductId = Products.Id
join InventoryLocationDetails on InventoryLocationDetails.InventoryLocationId = InventoryLocations.Id
where
Products.IsExpiryControlled = 1
and InventoryLocationDetails.ExpiryDate is not null
group by
Bins.BinCode, Zones.ZoneCode
order by
min(InventoryLocationDetails.ExpiryDate),
Zones.ZoneCode,
Bins.BinCode
8. Only this client's dedicated Bins
For 3PL companies: Empty bins whose client, or whose zone's client, is the product's client. Nothing shared and nothing of another client.
--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
Bins.BinCode + ' @ ' + Zones.ZoneCode
from
Bins
join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
join Products on Products.Id = @ProductId
left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
InventoryLocations.Id is null
and Zones.ProductHandling = 'ByProduct'
and coalesce(Bins.ClientId, Zones.ClientId) = Products.ClientId
order by
Zones.ZoneCode,
Bins.BinCode
9. Keep hazardous goods together
Hazmat Products go to Empty bins in Zones that already hold hazmat stock; other Products never go there. The first hazmat product finds no such zone, so the gun asks the operator to scan a bin.
--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
Bins.BinCode + ' @ ' + Zones.ZoneCode
from
Bins
join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
join Products on Products.Id = @ProductId
left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
InventoryLocations.Id is null
and Zones.ProductHandling = 'ByProduct'
and (
(Products.IsHazMat = 1 and exists (select 1 from Bins HazBins join InventoryLocations HazStock on HazStock.BinId = HazBins.Id join Products HazProducts on HazProducts.Id = HazStock.ProductId where HazBins.ZoneId = Zones.Id and HazProducts.IsHazMat = 1))
or (Products.IsHazMat = 0 and not exists (select 1 from Bins HazBins join InventoryLocations HazStock on HazStock.BinId = HazBins.Id join Products HazProducts on HazProducts.Id = HazStock.ProductId where HazBins.ZoneId = Zones.Id and HazProducts.IsHazMat = 1))
)
order by
Zones.ZoneCode,
Bins.BinCode
10. Full pallets to pallet Zones, loose units to shelving
When the quantity reaches the product's largest packsize, Zones that allow several pallets per bin; otherwise the other Zones. A product with no packsize counts as loose units.
Unit note: stock in the Bins is counted in eaches, so the query turns @Quantity into eaches by multiplying it by the each count of @PacksizeId (1 when the packsize is empty). Putting away 2 cases of 12 therefore compares 24 eaches, not 2.
--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
Bins.BinCode + ' @ ' + Zones.ZoneCode
from
Bins
join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
InventoryLocations.Id is null
and Zones.ProductHandling = 'ByProduct'
and Zones.AllowMultipleLpnsPerBin =
case when (@Quantity * coalesce((select ProductPacksizes.EachCount from ProductPacksizes where ProductPacksizes.Id = @PacksizeId), 1)) >= coalesce((select max(ProductPacksizes.EachCount) from ProductPacksizes where ProductPacksizes.ProductId = @ProductId), 2147483647) then 1 else 0 end
order by
Zones.ZoneCode,
Bins.BinCode
Was this page helpful?