Consolidate Rows - BAQ

Epicor ERP 10
1

Hello All. I’ve only been creating queries/reports for a few months now, so I’m not sure this is even possible.

I’m building a BAQ to show PO consumption percentages, that we can pull into BI and break out by year, quarter, month, etc… To do this, I’m trying to link our demand dates to show when the inventory will be consumed. When I link PartDtl to my top level query, I get the demand dates, but it’s returning duplicate rows for my release quantities as well.

I just want my query to show one row for my PODtl/PORel quantities, cost, vendor name, but show all of my demand dates per part.

Any help appreciated!

2

Here’s my expression. When I test, it’s returning duplicate rows based on the multiple demand dates.

select
[POHeader].[OrderDate] as [POHeader_OrderDate],
[PODetail].[PONUM] as [PODetail_PONUM],
[PODetail].[PartNum] as [PODetail_PartNum],
[PODetail].[LineDesc] as [PODetail_LineDesc],
[PODetail].[IUM] as [PODetail_IUM],
[PODetail].[UnitCost] as [PODetail_UnitCost],
[PORel].[OpenRelease] as [PORel_OpenRelease],
[PORel].[PORelNum] as [PORel_PORelNum],
[PORel].[DueDate] as [PORel_DueDate],
[PORel].[XRelQty] as [PORel_XRelQty],
[PORel].[ReceivedQty] as [PORel_ReceivedQty],
(PORel.XRelQty - PORel.ReceivedQty) as [Calculated_RemainingQty],
(RemainingQty * PODetail.UnitCost) as [Calculated_OpenAmount],
(datepart(quarter, PORel.DueDate)) as [Calculated_Quarter],
(Datepart(Month, PORel.DueDate)) as [Calculated_Month],
[POHeader].[BuyerID] as [POHeader_BuyerID],
[POHeader].[ApprovedBy] as [POHeader_ApprovedBy],
[Vendor].[Name] as [Vendor_Name],
[PartClass].[ClassID] as [PartClass_ClassID],
[PartClass].[Description] as [PartClass_Description],
[SubQuery2].[PartWhse_OnHandQty] as [PartWhse_OnHandQty],
[SubQuery2].[Calculated_TotalDemandQty] as [Calculated_TotalDemandQty],
(SubQuery2.Calculated_TotalDemandQty - (RemainingQty + SubQuery2.PartWhse_OnHandQty)) as [Calculated_Balance],
[SubQuery2].[PartDtl_DueDate] as [PartDtl_DueDate]
from Erp.PODetail as PODetail
inner join Erp.PORel as PORel on
PODetail.Company = PORel.Company
and PODetail.PONUM = PORel.PONum
and PODetail.POLine = PORel.POLine
and ( PORel.OpenRelease = 1 and PORel.Plant = @CurrentPlant )

inner join Erp.POHeader as POHeader on
PODetail.Company = POHeader.Company
and PODetail.PONUM = POHeader.PONum
inner join Erp.Vendor as Vendor on
POHeader.Company = Vendor.Company
and POHeader.VendorNum = Vendor.VendorNum
inner join Erp.PartClass as PartClass on
PODetail.Company = PartClass.Company
and PODetail.ClassID = PartClass.ClassID
left outer join (select
[PartDtl].[Company] as [PartDtl_Company],
[PartDtl].[PartNum] as [PartDtl_PartNum],
[PartWhse].[OnHandQty] as [PartWhse_OnHandQty],
(sum(PartDtl.Quantity)) as [Calculated_TotalDemandQty],
[PartDtl].[DueDate] as [PartDtl_DueDate]
from Erp.PartDtl as PartDtl
inner join Erp.PartWhse as PartWhse on
PartDtl.Company = PartWhse.Company
and PartDtl.PartNum = PartWhse.PartNum
and ( PartWhse.WarehouseCode = ‘01’ )

where (PartDtl.PONum = 0)
group by [PartDtl].[Company],
[PartDtl].[PartNum],
[PartWhse].[OnHandQty],
[PartDtl].[DueDate]) as SubQuery2 on
PODetail.Company = SubQuery2.PartDtl_Company
and PODetail.PartNum = SubQuery2.PartDtl_PartNum