Its a simple trick to query all those record whose current balance is less or equal to 0. Its a good one to see whether we should run Reorder or not.
Create a Save a Query with just a simple step.
- Click on where Clause in Advanced search button, and paste given code in it and then press Save Query Button, Enter Name (for example CURR BALANCE), Description and hit Save.
- Now Newly created query is displayed in your Action Menu. Just click on query name (CURR BALANCE in my case).
inventoryid in (select inventory.inventoryidfrom inventoryinner join invbalanceson inventory.siteid = invbalances.siteidand inventory.itemnum = invbalances.itemnumand inventory.itemsetid = invbalances.itemsetidand inventory.location = invbalances.locationand invbalances.curbal < 1)
the query needs to add a condition to select only items which is in marked as reorder
ReplyDeleteand
also some times the item exist in more bin and one of the BIN is zero balanc while still the Balance at the store level is more than zero
to group the Current balance in Invbalances table , then select where the current balance < 1 ,
the following view get the current balance at the store level
(create view [dbo].[curbal] as
select invbalances.itemnum,invbalances.location,siteid,sum(invbalances.curbal)curbal from invbalances
group by invbalances.itemnum,invbalances.location,invbalances.siteid)
then the query will use the view
(inventoryid in (select inventory.inventoryid
from inventory
inner join curbal
on inventory.siteid = curbal .siteid
and inventory.itemnum = curbal .itemnum
and inventory.location = curbal .location and inventory.minlevel>0
and curbal .curbal < 1 and inventory.reorder=1))