BAQ for oldest Open SO lines against recent receipts

Epicor ERP 10
1

I am trying to do a BAQ that shows all the open SO lines and joins then against recent receipts for those parts. I have criteria to show only SO lines and criteria on receipt detail if the receipt date is now -21 days. The query does work but it takes an age. I have done a sub query for SO data and sub query for receipt data and the top level combines the two. Any pointers?

Thanks

2

Hi Matt, can you past in your summary page of the query? It’s probably because you’re starting the query and the order and work down towards the invoice. You should reverse that and start at the invoice, join the order and then filter. It will be more “native” that way and work faster.

3

Hi Bob,

This is my query so far

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select 
	[SO_Open_Lines].[Customer_CustID] as [Customer_CustID],
	[SO_Open_Lines].[Customer_Name] as [Customer_Name],
	[SO_Open_Lines].[OrderDtl_OrderQty] as [OrderDtl_OrderQty],
	[SO_Open_Lines].[OrderDtl_PartNum] as [OrderDtl_PartNum],
	[SO_Open_Lines].[OrderHed_OrderDate] as [OrderHed_OrderDate],
	[SO_Open_Lines].[OrderHed_OrderNum] as [OrderHed_OrderNum],
	[SO_Open_Lines].[Part_PartDescription] as [Part_PartDescription],
	[Receipt_last_x_days].[RcvDtl_OurQty] as [RcvDtl_OurQty],
	[Receipt_last_x_days].[RcvDtl_PartNum] as [RcvDtl_PartNum],
	[Receipt_last_x_days].[RcvDtl_ReceiptDate] as [RcvDtl_ReceiptDate]
from  (select 
	[RcvDtl].[PartNum] as [RcvDtl_PartNum],
	[RcvDtl].[OurQty] as [RcvDtl_OurQty],
	[RcvDtl].[ReceiptDate] as [RcvDtl_ReceiptDate]
from Erp.RcvDtl as RcvDtl
where (RcvDtl.ReceiptDate >= dateadd (day, -21, Constants.Today)))  as Receipt_last_x_days
inner join  (select 
	[OrderHed].[OrderNum] as [OrderHed_OrderNum],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate],
	[Customer].[CustID] as [Customer_CustID],
	[Customer].[Name] as [Customer_Name],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[OrderDtl].[OrderQty] as [OrderDtl_OrderQty]
from Erp.OrderDtl as OrderDtl
inner join Erp.OrderHed as OrderHed on 
	OrderDtl.Company = OrderHed.Company
	and OrderDtl.OrderNum = OrderHed.OrderNum
inner join Erp.Customer as Customer on 
	OrderHed.Company = Customer.Company
	and OrderHed.BTCustNum = Customer.CustNum
inner join Erp.Part as Part on 
	OrderDtl.Company = Part.Company
	and OrderDtl.PartNum = Part.PartNum
where (OrderDtl.OpenLine = TRUE))  as SO_Open_Lines on 
	SO_Open_Lines.OrderDtl_PartNum = Receipt_last_x_days.RcvDtl_PartNum
order by SO_Open_Lines.OrderHed_OrderDate
4

Matt, I see one big performance hit right off. You see where it’s joining a subselect on the orderdtl and customer tables? These contain a huge amount of records and columns. If you could filter that down with the same criteria on the recv tables it would speed that part up. BAQ build queries in their own way which always isn’t great for performance.

1 Like
5

Thanks Bob. I made an assumption that since there was a filter on open so detail that would come through. So always filter where possible then. Much quicker now.