I’m not sure if this is the correct section of the forum to post this in, but I am attempting to develop a report in SSRS 2008 that details the quantities of a lot by bin location. I know I will have to manually join tables in a query to get the quantity outstanding per lot. I just need to know what tables those are and which fields to join the query on.
Thanks in advance.
You should actually be able to do this just with the Warehouse Entry table. It has the quantity, bin code, item number, lot number, etc.
I suppose I am wondering what to check against. Looking at the raw data on that table it appears to contain purchase, sales, and transfers. Each lot comes in on an initial purchase order so it will have a beginning quantity I need to check each additional entry against that lot with to make sure the report doesn’t display Lots in bins that are already on finalized sales orders, correct?
Warehouse Entry contains every movement into and out of a bin. You can just do a sum of the quantity field, grouped by Item No., Variant Code (if using), Lot No., and Bin.
If the Lot has already been complete sold, then the sum of the quantity will be zero.
Thanks! That worked great!!! [:D] You wouldn’t happen to know what table open and released sales order details reside on would you?
You can find out what table something is by opening up Navision, clicking on the record you want to know about, and entering Ctrl+F8 or Tools → Zoom. The title of the window that opens will contain the name of the table.
Open and Released Sales Order are contained in the Sales Header and Sales Line tables.
So technically, the quantity available for specific lot number from a specific bin to be placed on new sales orders is the summed quantity minus the summed quantity from a specific bin for the specific lot number on open and released sales orders? I’m just trying to verify some facts about the system before I go about querying and reporting the incorrect information.
This totally depends on how the company allocates inventory to orders. Your best bet is to actually sit down with the people that you are writing these reports for. If they can’t tell you how to manually calculate it then you’ll never be able to write a report, and they won’t be able to verify the data.
They currently aren’t using allocations, just placing them on Sales orders and either releasing them or leaving them open until delivery is confirmed. So until we change our processes this report should now accurately tell them the available (unreserved) lot contents in any bin.
Thanks for all the help Matt, I really really appreciate it!