Creating a GI for Warehouse Locations with 0 inventory on hand

I would try taking the INLocation and joining the Location Status to it based on the SiteID and LocationID, grouping the data on Site ID, and Location ID, and taking Max(QtyOnHand).

That should give you the list of all the locations and the max quantity in that location between the items.

Then you can put a shared filter on top of this GI to filter out any records where Max(QtyOnHand) > 0.

In SQL that will look something like below, but of course you need to take into account that you may need to use INLocationStatusByCostCenter instead of INLocationStatus depending on your Acumatica version.

select * from (select INLocation.CompanyID,INLocation.SiteID,INLocation.LocationID,MAX(QtyOnHand) as MaxLocationQty
from INLocation
left join INLocationStatus on INLocation.CompanyID=INLocationStatus.CompanyID and INLocation.SiteID=INLocationStatus.SiteID and INLocation.LocationID=INLocationStatus.LocationID
group by INLocation.CompanyID,INLocation.SiteID, INLocation.LocationID) as t
where MaxLocationQty is null or MaxLocationQty=0;