I briefly looked at your BAQ and suspect that you have mutliple Releases per your sales order lines?
To prove this, add the OrderRel table to your top level query and display the release numbers along with the warehouse.
And/or - PART PLANT subquery looks like the root of your problem - you could try adding the Sales Order LINE to that too?
Row multiplication resulting from mixing “dimensions” is something I see users doing all the time in Epicor. I usually start out I display header, line and releases in the raw data, then decide how to aggregate from there.
i.e. parts may be in many bins.
Rather than digging into the specific query, here is a marked up schema screen shot - where I try to display some causes for row multiplication - and pseudo subquery for Part Bin Qtys.
Also, do you have you have access to EpicWeb/EpicCare sites?
And have already downloaded the Epicor ICE - “Tools User Guide” and “Customization User Guide”.
Can be kind of a pain to go thru and read but… they do include some good BAQ examples, will show some grouping and aggregate calculations.
One Calc field for each warehouse, with the formulas:
SUM(case when PartBin.WarehouseCode = 'mfg' THEN PartBin.OnhandQty ELSE 0 end)
SUM(case when PartBin.WarehouseCode = 'CHAL2' THEN PartBin.OnhandQty ELSE 0 end)
The difference being which warehouse (mfg or CHAL2).
Then used that in place of the PartBin table in the top query:
with display fields:
The last two “calc fields” are actually just “fields” from the subquery2. No calculation exists in Subquery1 (the top level query) for those two. The two Calc above it do use fields from JobMtl and Subquery2.
This combines all the part bins for all warehouses into a single record, with individual columns for the warehouse QOH
In post 15, I was mistaken about only having one warehouse - Our production DB has just one per site, but our Test environment has multiple per site. Each row in the screenshot in post 15 was from a different warehouse. The warehouse names weren’t shown.
If i filtered my original BAQ (the one used for post 15) to just be warehouses mfg and CHAL2, then there would have only been 2 of the 6 rows shown.
Post 15 was mostly to show why one would get multiple rows.
After learning that the columns in the Original Poster’s screen shot were for specifc warehouses, I made the sub query so that it returned one row per partnum.