Hey ​@adharmawansha22 , Did you have any luck with this?

Hi,

You can try this, maybe don't have everything you need but is a beginning.

Thank you. Mine is essentially the same. The reason i asked, is i get inconsistent Locations ID’s for the generic inquiry vs Inventory Balance Report that is built in.

Here is an example.

From my GI:
 

From Inventory Balance Report

Stock Screen
 

Curious as to why it shows at N3 when my GI essentially mimics the Report. There must be something im missing or interupting?

EDIT:
Looking at your GI comparing mine, im thinking some of the relations are overlapping and maybe no even needed.

This reply has been created with the help of AI

To create a Generic Inquiry (GI) for Inventory Balance with transaction details such as receipts, adjustments, dates, batch numbers, and reference numbers, and to exclude landed cost variance details, you need to carefully select and join the appropriate database tables while applying the necessary filters.

Steps to Create the Inventory Balance GI:

1. Access Generic Inquiry Module:

   • Navigate to System Management > Generic Inquiry in Acumatica ERP.

2. Define the Base Tables: Use the following tables:

   • INTran: Contains detailed transaction records for inventory.
   • INRegister: Holds inventory transaction batches and header information.
   • InventoryItem: For item-specific details.
   • INLocation: For warehouse location details if required.

3. Configure Joins:

   • Join INTran with INRegister using the BatchNbr field.
   • Join INTran with InventoryItem using the InventoryID field.
   • Optionally, join INTran with INLocation using the LocationID field if location-specific data is needed.

4. Select Fields: Include the following fields in your inquiry:

   • Transaction Date: INTran.TranDate
   • Batch Number: INRegister.BatchNbr
   • Reference Number: INTran.RefNbr
   • Inventory ID: InventoryItem.InventoryID
   • Transaction Type: INTran.TranType (e.g., Receipts, Adjustments)
   • Quantity: INTran.Qty
   • Cost: INTran.UnitCost

5. Filter Out Landed Cost Variance: Add a condition to exclude landed cost variance transactions:

   • Use the INTran.TranType field and filter out the type associated with landed cost variance (e.g., TranType != 'LandedCostVariance').

6. Optional: Add Summarized Balances:

   • If you want to include inventory balances, use the INSiteStatus table and join it using the InventoryID and SiteID fields.

7. Sort and Group:

   • Add sorting based on transaction date (INTran.TranDate) and batch number (INRegister.BatchNbr).
   • Group by InventoryID if summarization is needed.

8. Test the GI:

   • Save and run the inquiry to validate the data.
   • Adjust filters or joins if unexpected results (like landed cost variance) appear.

SQL-Like Visualization of Joins and Filters:

sql Copy code

SELECT INTran.TranDate, INRegister.BatchNbr, INTran.RefNbr, InventoryItem.InventoryID, INTran.TranType, INTran.Qty, INTran.UnitCost FROM INTran JOIN INRegister ON INTran.BatchNbr = INRegister.BatchNbr JOIN InventoryItem ON INTran.InventoryID = InventoryItem.InventoryID WHERE INTran.TranType != 'LandedCostVariance' ORDER BY INTran.TranDate, INRegister.BatchNbr