BAQ not returning invoice with zero cost

Epicor ERP 10
1

We just switched over to E10 from E9 today, I created a BAQ in E9 for invoicing, we do a lot of EDI and this BAQ has been a miracle as far as c+p to EDI.

I had to recreate this BAQ in E10 because it did not pull over properly and all my data returned rows upon rows of repeated information.

I have it all correct now BUT the only problem is their is two invoices that are suppose to show but the invoice totals are $0, I can’t seem to understand what I need to do to get these to pull over.
select
[InvcDtl].[DiscountPercent] as [InvcDtl_DiscountPercent],
[InvcDtl].[PackNum] as [InvcDtl_PackNum],
[InvcDtl].[InvoiceLine] as [InvcDtl_InvoiceLine],
[InvcDtl].[OurShipQty] as [InvcDtl_OurShipQty],
[InvcDtl].[SalesUM] as [InvcDtl_SalesUM],
[InvcDtl].[DocUnitPrice] as [InvcDtl_DocUnitPrice],
[InvcDtl].[IUM] as [InvcDtl_IUM],
[InvcDtl].[PartNum] as [InvcDtl_PartNum],
[InvcDtl].[LineDesc] as [InvcDtl_LineDesc],
[Customer].[CustID] as [Customer_CustID],
[Customer].[BTName] as [Customer_BTName],
[ShipHead].[TrackingNumber] as [ShipHead_TrackingNumber],
[ShipTo].[ShipToNum] as [ShipTo_ShipToNum]
from Erp.InvcHead as InvcHead
inner join Erp.InvcDtl as InvcDtl on
InvcDtl.Company = InvcHead.Company
and InvcDtl.InvoiceNum = InvcHead.InvoiceNum
inner join Erp.ShipHead as ShipHead on
InvcDtl.Company = ShipHead.Company
and InvcDtl.PackNum = ShipHead.PackNum
inner join Erp.Customer as Customer on
Customer.Company = ShipHead.Company
and Customer.CustNum = ShipHead.CustNum
inner join Erp.ShipTo as ShipTo on
ShipTo.Company = ShipHead.Company
and ShipTo.CustNum = ShipHead.CustNum
and ShipTo.ShipToNum = ShipHead.ShipToNum
where (InvcHead.GroupID = @GroupID)

2

Are you sure the $0 is linked to a shipment? Does it show if you change your shipment tables to left outer?

1 Like
3

The fix was THAT simple, I’ve been running circles on this BAQ all day LOL.

Yes, there is a shipment tied to it, but thank you! It worked doing a left outer join on ship to table

1 Like