If you stock product, you’re likely familiar with the Release to On-Hand report. Used to put product away, it’s got a couple flaws for me. First, for my company at least, the report’s almost too small to read. While I could have a look at reformatting the report so it’s readable, it’s got another problem that makes it a deal-breaker: It doesn’t show bulk bins. But we have a way around that with the PO Bin report.
We’ll start with the shortcut version – the modified warehousereports.php, warehousereports.js, and the PO Bin report itself are posted here – bendustries.co/files/warehouse/warehousereports_inc_pobin.zip. The zip file contains two versions of pobin.php – one that includes bulk bins (from the same MySQL table as we used in Item Bin), and one without. I’ve defaulted this time to the report without bulk bins, as I think it more likely that other dealers are more likely not using them. Additionally, this query uses no inventory location, so keep that in mind should you maintain inventory in multiple locations.
The query we’re using is very simple:
select PurchaseOrderDetail.SKU, PurchaseOrderDetail.MACCode, PurchaseOrderDetail.Ordered,
PurchaseOrderDetail.Received, ItemInventory.PrimaryBin, ItemInventory.SecondaryBin, ItemInventory.ItemId
from PurchaseOrderDetail full join ItemInventory on PurchaseOrderDetail.ItemId = ItemInventory.ItemId
where PurchaseOrderDetail.PurchaseOrderId like (?)
A note on the (?) you see at the end of the query – we’re using parameterized queries. An excellent explanation of why it’s important is located at http://blogs.msdn.com/b/sqlphp/archive/2008/09/30/how-and-why-to-use-parameterized-queries.aspx. Essentially, we’re preventing SQL injection attacks, where malicious users can attack your SQL Server. We probably don’t have to be terribly concerned here, as it will only be your own employees accessing these reports. Just the same, we’ll play it safe.
To pass the PO number into the query, we use two lines of code:
$po = sanitize($_POST['pbrPONumber']);
$po = '%'.$po;
Then, when we run the query, we specify the parameters using
$params = array($po);. This way, we can reuse the line that runs the query,
$getReport = sqlsrv_query($idConn, $tsql, $params, $options);.
When you run this report, you’ll see a result something like this:
Notice that we sort it in bin order. Incidentally, if you hadn’t noticed before, you can resort these tables on any column. We default this one to bin order, since it probably makes the most sense to put the product away front to back, in order. To do that, you’ll likely want to print the report out, which leads me to the next feature I haven’t really shown yet:
Notice that only the body of the report shows. Using a little css trickery, we hide the reporting interfaces to allow the report alone to print. Using this instead of the Release to On-Hand report should be much easier on your warehouse crew.
So there you have a new put-away report, PO Bin. As my people use it every day, I would hope it is useful to your business, as well. As always, if you need my help, find me at