I’m trying to write a simple query to return the PerCon information associated with entries in the QuoteCnt table.
This sounds pretty simple, however I’m running into difficulties with the way that these are actually stored.
From this screenshot, you can see that this quote has 3 contacts added to it. These are actually some unholy mixture of CustCnts and QuoteCnts that appear on the surface to all be from a homogeneous data source.
From the UI, it looks like the prime contact has a PerConID associated with it.
If we query the QuoteCnt table in SQL, we see that this is not storing the PerConID
Clearly, it’s not storing the PerConID in the table as I thought.
The intent is to simply query this list of data as it appears in the UI so that I can return basic information about that PerCon.
What am I missing?
Looks like if I join QuoteCnt to PerCon on name, it’ll work but holy moly, that’s a filthy way to accomplish that.
My take on this is that the quotecnt is supposed to look at the CustCnt.CustNum,CustCnt.ShiptToNum,CustCnt.ConNum and then you can use the perconid from the custcnt to look at percon or you can use the PerConLnk to be the intermediate table between the CustCnt and the Percon table. The QuoteHed.PrcConNum may also come into play which might be why the PerConID for the third record in your output has a 0 in it. Do you have a value in the QuoteHed.PrcConNum that matches that third row?
One thing I will say is that it is convoluted to say the least. There was an issue in E9 that allowed you to enter in a contact in the quote by name and it would duplicate the contact in the Customer Contact table. We used case management heavily and boy did our support staff get frustrated when two or more contacts with same name came up when they were trying to allocate a case.
On a point to note I looked at the Epicor 10 DataMap v3.0 dated 2016 and the PerConlnk table is not referenced and in the QuoteHed table a field named PerconLnkRowID is mentioned, but it is not present in my 101.600 database.
Not sure if I have helped any here. anyway I hope you find the input useful.
I wonder if its QuoteCnt -> CustCnt -> PerCon