Joining two tables

Epicor ERP 10
, , ,
1

I am trying to join QuoteHed and OrderHed in a BAQ, and I cannot seem to find the right fields to use to join on. We are trying to track quotes that turn into WINS. I have a query that works now, but management wants to add in Customer Name and the Salesman that converted it. The problem is that on the quote there really is no customer name other than an epibinding field. Not sure how to use that in a BAQ. Same with the salesman. That field in field help is an epibinding to QuoteHed.SalesRepCode. Can anyone provide insight on how to do this?

This is what I have so far.

select distinct
	[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum],
	[QuoteHed].[CustNum] as [QuoteHed_CustNum],
	[QuoteHed].[EntryDate] as [QuoteHed_EntryDate],
	[QuoteHed].[DocTotalGrossValue] as [QuoteHed_DocTotalGrossValue],
	[Task].[Conclusion] as [Task_Conclusion]
from Erp.QuoteHed as QuoteHed
inner join Erp.Task as Task on 
	QuoteHed.SysRowID = Task.RelatedToSysRowID
	and ( Task.NextTaskSeq = 0  and Task.Conclusion = 'WIN'  )

where (QuoteHed.EntryDate > @StartDate  and QuoteHed.EntryDate < @EndDate)```
2

Join Customer to QuoteHed on CustNum

SaleRep will likely be more of a challenge. Look at the data in the salesrep field. If it is tilde delimited you will need to do a split to get the first code and then join on that.

3

For Quote sales rep you have to look at the QSalesRep table. The EpiBinding QuoteHed.SalesRepCode is not a db field, it becomes the db record where QSalesRep.PrimeRep = true.

Also the link between quotes and orders lives at the line level. If you look at OrderDtl you should find QuoteNum and QuoteLine fields.

1 Like
4

Thanks for all the help. Ended up going with this, and it works really well.

SELECT
  HED.QuoteNum,
  HED.CustNum,
  HED.EntryDate,
  HED.QuoteComment,
  TSK.Conclusion,
  OHD.OrderDate,
  ORD.OrderNum,
  ORD.OrderLine,
  CST.Name AS [Customer],
  REp.Name AS [SalesRep]
FROM QuoteHed HED
JOIN QuoteDtl QTE
ON QTE.QuoteNum = HED.QuoteNum
AND QTE.Company = HED.Company
JOIN OrderDtl ORD
ON ORD.QuoteNum = QTE.QuoteNum
AND ORD.QuoteLine = QTE.QuoteLine
AND ORD.Company = QTE.Company
JOIN OrderHed OHD
ON OHD.OrderNum = ORD.OrderNum
AND OHD.Company = ORD.Company
JOIN Erp.Task AS TSK
ON TSK.RelatedToSysRowID = HED.SysRowID
AND TSK.Company = HED.Company
JOIN Customer CST
ON CST.CustNum = ORD.CustNum
AND CST.Company = ORD.Company
JOIN SalesRep REP
ON REP.SalesRepCode = TSK.SalesRepCode
AND REP.Company = TSK.Company
WHERE TSK.NextTaskSeq = 0