In this blog post, I will try to cover how to build saved search to display bin level information by item where there could be multiple bins at same location for same item.
The plan is to find out which bin (that has stock at a particular location) can be considered to be treated to become primary bin and also which bin can be considered to be made inactive.
This will help the warehouse or inventory managers to consistently review this data and take necessary actions with regards to bin management.
Let us start with ITEM based saved search.
Within the criteria add filters such as item is active, consider bins with onHand qty greater than zero (this way we do not show zero stock bins in this saved search and if required they can be added or provided as another saved search), specify which location. If you want to check for all locations, they you can specify all of them.
Criteria
Item.Inactive is false
BinOnHand.OnHand is greater than zero.
BinOnHand.Location is NYC,LASVEGAS
Results
Name – Item number/name
BinOnhand.Binnumber
binONhand.location
binOnHand.available
binONHand.OnHand
Create formula field where we check what needs to be done with the bin now.
(case when (({binonhand.location} = ‘NYC’ OR {binonhand.location} = ‘LASVEGAS’) ) then
(case when (nvl(DENSE_RANK() OVER (PARTITION BY {internalid},{binonhand.location}
ORDER BY {binonhand.quantityonhand} desc, {binonhand.binnumber} desc) ,0) = 1 OR {binonhand.quantityonhand} > 0) THEN ‘RETAIN’ ELSE ‘EMPTY’ END) else ‘RETAIN’ END
)
Explanation of the formula:
Here, I am checking if location is NYC or LASVEGAS but you can remove it if you want to check for all locations.
After this the next condition checked is to find out if for the itemInternalId, binOnHandLocation , whether the bins have stock and if there is no stock, whether it is good to keep or not.
The DENSE_RANK and PARTITION BY are similar to ORACLE functions and will help to use determine our condition.
There may be other conditions that you want to check here such as to find out how many items are in same Bin. This also could be a consideration for some inventory managers whether or not to keep bin. Below is the formula for this condition.
(count/* comment */({internalid}) OVER(PARTITION BY {binonhand.binnumber}))
Also, some inventory managers may want to keep the bin as required even though there is no stock but it has preferred bin ticked. The preferred bin is a Boolean value that is available within binNUmber join.
So, there are few ways to find out how to find out which bins are out there, what is required to be kept and what can be made empty or deleted.
Please check the formulas and test them.
Feel free to contact us for any questions.
