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.

Wait! Before You GoWould you like to talk to a business software expert to answer your questions?

When you schedule a free consultation with MIBAR, you will experience a one-on-one conversation with a business technology expert who is passionate about understanding your unique needs or issues. Schedule a free consultation today at the link below or give us a call at (212) 869-9300.

Learn More