How to create a GI to show Qty on Hand and Sold Qty

Userlevel 1

Hi Team,

We have a requirement where we have to create a GI and have to show Qty on Hand across all warehouses and Sold Qty (Qty across all released Invoices) for stock items. We created a GI and for all new products it is working fine but for existing products the values are incorrect. I am attaching the created GI, can you please review and suggest the best way possible to achieve it.

Thank you!



Best answer by Manikanta Dhulipudi 29 May 2023, 18:12

View original

3 replies

Userlevel 7
Badge +12

Hi @santhu3242 

Please find the attached GI which pulls sold and Onhand qty which helps to fulfil your requirement or compare the GI with your GI.

Userlevel 1

Thank you @manikantad18  for the response, we verified with suggested input and getting the data as expected 

Now the details are showing based on individual warehouse but we need to show one line for one product and have to show sum of Qty on Hand across all warehouses and sum of all sold qty across all warehouse and released invoices.

Can you suggest to achieve this and help with that please.

Userlevel 5
Badge +1

@manikantad18  - Can you help me understand what might be happening here


I’m trying to use your GI to pull in total sales + usage in assembly during a given time period and then compare that to our total OH + total on PO. I’ve given the GI parameters for the date range using INItemSiteHistDay.SDate and warehouse from the INSiteStatus.SiteID, which all seems to work great


When I try to create calculations using values from the INItemSiteHistDay table it doesn’t seem to be pulling in the sum value and instead relies on the first value it’s seeing


Below you see the Sales from INItemSiteHistDay.qtySales as 45 which is correct per the Inventory Transaction History on the side panel (30 +10 + 5) and you see the INItemSiteHistDay.qtyAssemblyIn correctly at 52


However - Total Sold is being calculated as =([INItemSiteHistDay.QtySales]+ [INItemSiteHistDay.QtyDropShipSales])-[INItemSiteHistDay.QtyCreditMemos] which in this case should equal 45 since there are no drop shipments or credit memos. The inquiry is returning the first value in the history (in this case 30). 


Total Used - =([INItemSiteHistDay.QtySales]+[INItemSiteHistDay.QtyDropShipSales]+[INItemSiteHistDay.QtyAssemblyOut])-[INItemSiteHistDay.QtyCreditMemos]

is trying to add in QtyAssemblyOut to the sales usage - which in this case should be 97 but it is only returning the first value in Assembly Out (52)


It gets even worse when I try to subtract Total Used from the Qty On Hand and then add back in Total on PO



About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved