Inventory on Dispatch Report
I am looking to see if anyone has come up with a report to query inventory that is on a dispatch that has not been invoiced.
Some background -
On all of our dispatches, our Warehouse Manager includes the inventory items used on the dispatch in the "Parts" tab. When I do a physical inventory, all of these inventory items on the dispatches in the "Parts" tabs are not in my Inventory room, but are out on these dispatches. However, these items show in my Inventory count as if they are sitting in the Inventory room.
It would be so awesome if there was a report or query I could run to see what inventory items are on the dispatches under the "Parts" tab. Currently, I have to do a reconciliation between the Actual Inventory number and add in all the "Parts" on each individual dispatch. It is so easy to miss a dispatch that has parts on it.
Any suggestions/ideas??
Comments
Hi, If you are asking for an actual query to be run outside of ESC in something like SQL Server Management Studios (SSMS) Then this might be helpful for you.
SELECT convert(date,RecDate,111)"First Contact Date",Dispatch.Dispatch,Prod, "Desc", Quan, Price
FROM dispatch inner join
DispParts on DispParts.Dispatch = Dispatch.Dispatch
WHERE invoice =''
This query will find all dispatches that do not have invoices yet, and pull the parts records for those dispatches. As I created it based on your information there might be some tuning or changes that need to happen to the query. For instance, as soon as a tech adds an item to the invoicing screen and exits out (whether they complete it or not) the dispatch and parts will no longer appear in this list. I can modify it that once the invoice is imported then it will disappear from the list, but as it stands now the list will only show dispatches that have no invoice items attached to them.
Let me know if it helps or is what you were looking for. Thanks
Meghan,
I thought I had replied to a very similar post, but I can't find it. Regardless, the simplest way I know of to solve this issue is the use of staging bins (read warehouses). I our case this is a caged area in the shop with various sized cubby holes and some shelving.
When we have parts that are assigned to a dispatch one of two things should happen. Either you transfer the parts to either the tech's van, or to a staging bins for a bigger job or quoted work. This gets it out of your main stock so you know it is no longer available.
My current (as of the new year) policy is that nothing, ever, gets invoiced out of warehouse 0000 (main stock). The only exception is over the counter POS sales, because they are done at the moment the parts are removed physically from stock.
We've fought enough problems, operator and other, that it's critical that physical and electronic transfers must happen at the same time from main stock.
For the techs we do have a sign out sheet on the stock room door. Our parts lady keeps it current with transfers to the appropriate Van.
So we have somewhere on the order of 100 warehouses in the system to track where our inventory actually is. It works, it is a bit of overhead, but it works. And minimizes invoicing and inventory issues.
One of the main reasons for not allowing invoicing from main stock is that when Accounting can't figure out where a part is coming from, they'd just pull it from 0000. Which throws of your counts in 2 locations. Trying to figure out why and what happened 3 months later is a real pain.
Hope this helps. Looking above it's a bit of a ramble, if you need clarification, let me know.
Rick
Have any suggestions for managing accurate warehouse inventory and truck inventory when importing invoices is behind and when dispatches are held on the board for return trips to complete the job (the incomplete dispatch)?
Don't let importing mobile invoices get behind. When Accounting is behind, have dispatch import them and put them on hold, that will pull the parts from stock. They will have to confirm the warehouse to pull from.
As far as items still on the board, there isn't a good answer as far as I know.
I tell our techs that if they use a commonly used part to ask one of us for a replacement even though the system hasn't caught up. The other answer is to increase the counts of some parts on the van to compensate for delays.
Hope this helps.
Rick
Please sign in to leave a comment.