As dynamic and robust of a system that Acumatica is, there are several basic inquiries and functions that have come up during my time implementing the software that are not out-of-the-box. Having implemented Acumatica for a few manufacturing and distribution companies, managing inventory levels has been of key importance. Surprisingly enough, there is not a native Generic Inquiry that can display On Hand and Available Quantity for Stock Items by Bin and by Lot/Serial Number. There are a few alternate methods by which users can determine this information, but each comes with its own setbacks. For example, the user can navigate to the Inventory Summary inquiry and ‘Expand By Lot/Serial Number’:

Inventory SummaryHowever, the key setback of this inquiry is that the results are provided on an item-by-item basis. The user must search for each item individually. What happens if the user would like to view a batch of items at once? A solution would be to run the ‘Inventory Balance Report’ and ensure that the ‘Location Details’ and ‘Lot/Serial Details’ checkboxes are selected:

Inventory BalanceHere the user could apply whatever filters they like, but the biggest downside is that this report is much more difficult to customize and manipulate. Unlike Generic Inquiries, where directly through the user interface users can move/hide columns, join tables for additional related data, etc. customization of this report would require the assistance of a developer. Another downside of this report is that exporting to Excel is not the cleanest. My clients have often exported data from Excel to further view and manipulate data. Exporting this report and manipulating the data in Excel is quite difficult due to the formatting of the export.

There is a very simple Generic Inquiry that can be created using the ‘PX.Objects.IN.INLotSerialStatus’ and ‘PX.Objects.IN.InventoryItem’ tables to display Items with their On Hand and Available quantities by Lot/Serial Number and Bin. See the Tables, Relationships, and Results below:

Generic InquiryThis Generic Inquiry can be used as the starting point for a wide variety of variations. For example, users could easily join to the Item Class and Warehouse Tables, add conditions and filters, etc. to create more robust versions of this search while still maintaining the high usability and flexibility of Generic Inquiries.

Hope that helps! For Acumatica customizations or support, contact us today!