BAQ giving me Duplicate lines in data

Epicor ERP 10
1

Hi All

I have cobbled together a sub con received BAQ it works great but im getting duplicates data i test the the baq.

Any help would be great i betting is something really simple Ive missed

	[RcvDtl].[PONum] as [RcvDtl_PONum],
	[RcvDtl].[POLine] as [RcvDtl_POLine],
	[Vendor].[Name] as [Vendor_Name],
	[RcvDtl].[ReceiptDate] as [RcvDtl_ReceiptDate],
	[RcvDtl].[PackSlip] as [RcvDtl_PackSlip],
	[RcvDtl].[PartNum] as [RcvDtl_PartNum],
	[RcvDtl].[PartDescription] as [RcvDtl_PartDescription],
	[RcvDtl].[OurQty] as [RcvDtl_OurQty],
	(PORel.RelQty - PORel.ReceivedQty) as [Calculated_OutstandingQty],
	[RcvDtl].[JobNum] as [RcvDtl_JobNum],
	[RcvDtl].[LotNum] as [RcvDtl_LotNum],
	[RcvDtl].[InspectionReq] as [RcvDtl_InspectionReq],
	[POHeader].[OrderDate] as [POHeader_OrderDate],
	[SubShipD].[PackNum] as [SubShipD_PackNum],
	(case 
     when PORel.DueDate IS NULL then
       case 
           when PODetail.DueDate IS NULL then
             case
               when PORel.DueDate IS NULL then
                 POHeader.DueDate
               else
                 PORel.DueDate
               end
           else
             PODetail.DueDate
       end
     else
     PORel.DueDate
 end) as [Calculated_DueDate]
from Erp.RcvDtl as RcvDtl
inner join Erp.RcvHead as RcvHead on 
	RcvDtl.Company = RcvHead.Company
	and RcvDtl.VendorNum = RcvHead.VendorNum
	and RcvDtl.PurPoint = RcvHead.PurPoint
	and RcvDtl.PackSlip = RcvHead.PackSlip
inner join Erp.Vendor as Vendor on 
	RcvHead.Company = Vendor.Company
	and RcvHead.VendorNum = Vendor.VendorNum
inner join Erp.PODetail as PODetail on 
	RcvDtl.Company = PODetail.Company
	and RcvDtl.PONum = PODetail.PONUM
	and RcvDtl.POLine = PODetail.POLine
inner join Erp.PORel as PORel on 
	PODetail.Company = PORel.Company
	and PODetail.PONUM = PORel.PONum
	and PODetail.POLine = PORel.POLine
inner join Erp.SubShipD as SubShipD on 
	PORel.PONum = SubShipD.PONum
	and PORel.POLine = SubShipD.POLine
	and PORel.PORelNum = SubShipD.PORelNum
left outer join Erp.POHeader as POHeader on 
	POHeader.Company = PODetail.Company
	and POHeader.PONum = PODetail.PONUM
	and ( POHeader.OpenOrder = 1  )

where (RcvDtl.JobNum <> '""')
2

It is hard for me to read SQL, but I would bet that you have recevied multiple lines on the receipt. Look in your output data. The rows should be identical when duplicated, except for the field that is making them unique. Sometimes you haven’t added the field that makes them unique, only the table that makes them unique. If you suspect a table you added made the duplicate rows, look for key fields in the new table, and make sure those are in your output. That way you can see what field is making the row unique.
Is it the line number of either RcvDtl? It could also be the PORelNum. Those are the most common culprits for me with these tables.
Good luck!

5 Likes
3

In looking at your SQL, I would try joining the RcvDtl to the PORel instead of PODtl.

3 Likes
4

My usual culprit when I am trying to join multiple tables and getting duplicates is my joins not being specific enough so it “duplicates” but it’s actually matching data incorrectly.

2 Likes
5

Do you have multiple releases on the PO Release?
If so, that would be the cause.

2 Likes