Multiple lines in BAQ to consolidate into 1 line item

Epicor ERP 10
1

Hi all,

I had a similar issue to this post here:

I followed the steps but didn’t give me the solution I needed.
I want to find the Invoice numbers linked to PO lines. And since there can be multiple invoices for a PO line, I would like the results to be seen in one line instead of multiple lines.
Please help if you can fix this.

Thanks
Himal

select 
	[PORel].[PONum] as [PORel_PONum],
	[PORel].[POLine] as [PORel_POLine],
	[PORel].[PORelNum] as [PORel_PORelNum],
	[PORel].[RelQty] as [PORel_RelQty],
	[PORel].[ReceivedQty] as [PORel_ReceivedQty],
	[PODetail].[PartNum] as [PODetail_PartNum],
	[PODetail].[LineDesc] as [PODetail_LineDesc],
	(REVERSE(stuff(REVERSE(REPLACE(REPLACE(((select 
	(cast(APInvDtl.InvoiceNum as varchar) + ', ') as [Calculated_Invoices]
from Erp.APInvDtl as APInvDtl1
where (APInvDtl1.PONum = APInvDtl.PONum FOR XML PATH('')))) , '</Calculated_Invoices>',''),'<Calculated_Invoices>','')), 1, 1, ''))) as [Calculated_Invoices_]
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.RelQty = PORel.ReceivedQty  and PORel.PONum = 108661  )

inner join Erp.APInvDtl as APInvDtl on 
	APInvDtl.Company = PORel.Company
	and APInvDtl.PONum = PORel.PONum
	and APInvDtl.POLine = PORel.POLine
	and APInvDtl.PORelNum = PORel.PORelNum

baq1
baq11224×2167 161 KB

2

Asked and answered b4.

3 Likes