How to Create a Generic Inquiry to display Stock item's Quantity on Hand in all warehouses without using a Grouping.

​@lorach Business events will not work when we use grouping.

Hi Lorach,

Please try steps below.

1. Modify the Generic Inquiry (GI):

   - Open your existing GI that displays stock items and their quantities on hand.

   - Ensure the GI includes the following fields:

     - `InventoryItem.InventoryCD` (Stock Item ID)

     - `InventoryItem.Descr` (Item Description)

     - `INSiteStatus.QtyOnHand` (Quantity on Hand)

     - `INSiteStatus.SiteID` (Warehouse Location)

   - Remove the grouping on `InventoryItem.InventoryCD` to allow the GI to return individual rows for each warehouse location.

2. Add a Calculated Field for Total Quantity:

   - In the GI, add a calculated field to sum up the `QtyOnHand` for each item across all warehouses.

     - Go to the Results Grid section of the GI.

     - Add a new calculated field with the expression:

       ```sql

       SUM(INSiteStatus.QtyOnHand)

       ```

     - Set the Group By option for this field to `InventoryItem.InventoryCD`.

   - This will allow you to group by item while still calculating the total quantity on hand across all warehouses.

3. Filter for Zero Quantity Items:

   - Add a filter to the GI to only include items where the total quantity on hand is zero.

     - In the Filters section, add a condition:

       ```sql

       SUM(INSiteStatus.QtyOnHand) = 0

       ```

   - This ensures the GI only returns items that meet the criteria for inactivation.

4. Save and Publish the GI:

   - Save the changes to the GI and publish it.

5. Create or Update the Business Event:

   - Open the business event that will use this GI to inactivate items.

   - Set the GI as the data source for the business event.

   - Configure the business event to:

     - Iterate through each row returned by the GI.

     - Update the `InventoryItem` record to set the `ItemStatus` to "Inactive" for items with zero quantity on hand.

6. Test the Business Event:

   - Run the business event in a test environment to ensure it correctly inactivates items with zero quantity on hand.

   - Verify that the GI and business event work together as expected.

Note: This response was generated with the help of an AI tool

Hi ​@Manikanta Dhulipudi,Yes, I noted that. The issue is achieving such a GI requirement without the need for grouping.

Hi ​@lorach 

If we remove Grouping by “InventoryItem.InventoryCD” and add the condition ItemQtyCost.QtyOnHand Does Not Equal 0
Then the data will be displayed according to the Warehouses & Serial Number, not duplicated.

Best Regards,

NNT

Hi ​@lorach 

Can you elaborate the business requirement? You have existing reports which gives you the On-hand quantity across warehouses. You may check Inventory Balance report ( or Inventory valuation ) ScreenId=IN615000

Hope this helps,

Regards,

Hi ​@nhatnghetinh, the condition eliminates the duplicates, but it lists qties on a warehouse basis i.e. if the same item is in two warehouses A with 2 qties and B with 1qty it will be listed twice to cater for the two warehouses, so the grouping was aiding in listing that item in one line having 3 qties, accounting for the sum of all qty on hand for that item in the various warehouses, the screenshots below elaborate it well, first one shows the item on the GI with the grouping enabled and the other without. So the issue with your suggestion comes in when it’s time for the BE to inactivate the item because the way it has been configured, it gets triggered once the item line on the GI hits zero, in the scenario where the qties go to zero in one warehouse it will be inactivated even when there are other qties in a separate warehouse.

Hi ​@lorach ,

Depending on your business requirements, if the update doesn't have to be in real-time, an idea is to run the automation on a schedule using the Process Import Scenarios screen instead of a business event.  On the schedule you should be able to specify the filter values and conditions to process only the records where the aggregated sum is 0.

Hope this helps!

Laura 

​