Multi Warehouse Part Qty BAQ

Epicor ERP 10
,
1

Hello all. I am working on a simple BAQ to display on hand quantities for every part in a certain class. We have 2 warehouses we use in Epicor and I would like them to be in separate columns rather than separate rows. To get this to work I added the PartQty table twice and set a criteria for each table with their own warehouse code. The issue I am running into is if a part has never had any inventory history in one of the two warehouses, it will not show up. So out of 400+ parts in this class, only 20 records show up in my query because they are the only ones with history in both warehouses. Is there a way to get it to show a zero balance if there is no record in that table? I have attached my query below. Thanks!

select
[Part].[PartNum] as [Part_PartNum],
[PartQty].[OnHandQty] as [PartQty_OnHandQty],
[PartQty].[WarehouseCode] as [PartQty_WarehouseCode],
[PartQty1].[OnHandQty] as [PartQty1_OnHandQty],
[PartQty1].[WarehouseCode] as [PartQty1_WarehouseCode]
from Erp.Part as Part
inner join Erp.PartQty as PartQty on
Part.Company = PartQty.Company
and Part.PartNum = PartQty.PartNum
and ( PartQty.WarehouseCode = ‘FACTORY’ )

inner join Erp.PartQty as PartQty1 on
Part.Company = PartQty1.Company
and Part.PartNum = PartQty1.PartNum
and ( PartQty1.WarehouseCode = ‘WH’ )

where (Part.ClassID = ‘UWRT’)

2

You don’t need to join PartQty twice. In fact, that’s what is messing you up.

Use a calculated field (one for each whse/column) with a CASE statement to turn different warehouses into columns. (This is sometimes called a manual pivot).

--First calculated field
SUM(CASE WHEN PartQty.WarehouseCode = ‘FACTORY’ THEN PartQty.OnHandQty ELSE 0 END)

--Second calculated field
SUM(CASE WHEN PartQty.WarehouseCode = ‘WH’ THEN PartQty.OnHandQty ELSE 0 END)

Remove the Warehouse code columns. Then group the columns by Part.PartNum.

1 Like
3

Awesome, that’s exactly what I was looking for but didn’t know how to do it. I’m pretty new at BAQ’s and SQL code. Thank you so much!