Different Results with BAQ & SQL Query & External Query

Kinetic ERP
1

I’m getting different results between each of these methods of getting data. The only one that gets accurate data is the SQL query. (In sql I am just changing the date parameter to a static date. That is the only difference.)
The issue seems to happen when grouping certain fields in the ARMovement table.

What I am attempting to do is create an AR query that reconciles for Central Collections by child company.

My BAQ seems to work just fine for previous months and begins to have issues starting in May. This is the first month that it appears to be missing records. (3 to be exact).

Any ideas?

select 
	[ARMovement].[Company] as [ARMovement_Company],
	[Customer].[CustID] as [Customer_CustID],
	[Customer].[Name] as [Customer_Name],
	[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
	[InvcHead].[CColChildCompany] as [InvcHead_CColChildCompany],
	(SUM(ARMovement.TranAmt)) as [Calculated_InvoiceBal]
from Erp.ARMovement as ARMovement
left outer join Erp.InvcHead as InvcHead on 
	InvcHead.Company = ARMovement.Company
	and InvcHead.InvoiceNum = ARMovement.InvoiceNum
	and ( InvcHead.ApplyDate <= @Date  and (InvcHead.OpenInvoice = 1  or InvcHead.ClosedDate > @Date ) )

inner join Erp.Customer as Customer on 
	(InvcHead.Company = Customer.Company
	and InvcHead.CustNum = Customer.CustNum)
where (ARMovement.TranDate <= @Date)
group by [ARMovement].[Company],
	[Customer].[CustID],
	[Customer].[Name],
	[InvcHead].[InvoiceNum],
	[InvcHead].[CColChildCompany]
2

One of the things that the BAQ does that SQL does not is add logic for territory access. If you don’t have access to the customers for the missing records, that could be one possible explanation.

2 Likes
3

Definitely 100% right @Mark_Wonsil… @Anthony_Gercar best suggestion I can give is use Profiler to compare the two queries, you will see all the territory stuff in the query generated by the BAQ.

1 Like
4

Relevant to the discussion:
https://www.epiusers.help/t/sql-for-running-queries-just-like-epicor/102127?u=klincecum

1 Like
5

Good idea, however, that is why I decided to look at the external baq. That shouldn’t care about territories or any of that logic since you are going straight to the data source as you would in SQL.

6

I’m going to check this out.
Thanks!!