Sales Order Contact # confusion

I am stumped and hope someone can help me. I need to get the email address of the Sold To: Attn contact on the sales order. Field Help says the field that stores this value is OrderHed.PrcConNum. No matter what name I select from the drop down the number is always 1.

To make things worse, when I join the Orderhed table to CustCont table using CustNum->CustNum and PrcConNum->ConNum I get 2 rows.

Any help would be greatly appreciated… Thanks.

Include the ShipTo value in your join. If a customer has multiple ShipTos, and contacts per ShipTo, you will get multiple results.

Thanks Andrew for the help. I did as I understood you to say…

select
[OrderHed].[PrcConNum] as [OrderHed_PrcConNum],
[CustCnt].[ConNum] as [CustCnt_ConNum]
from Erp.OrderHed as OrderHed
inner join Erp.CustCnt as CustCnt on
OrderHed.CustNum = CustCnt.CustNum
and OrderHed.PrcConNum = CustCnt.ConNum
and OrderHed.ShipToNum = CustCnt.ShipToNum
where (OrderHed.OrderNum = 1344)

Now now records are returned… Could something be messed up with my customer record?

Thanks Again.

To be more clear… I am trying to return the Sold To: Attn contact email address

Your query looks correct. It’s good practice to always include the Company field in any joins as well, but with it missing, that doesn’t explain you getting no results.

Here’s my query:

select 
	[OrderHed].[Company] as [OrderHed_Company],
	[OrderHed].[OrderNum] as [OrderHed_OrderNum],
	[OrderHed].[CustNum] as [OrderHed_CustNum],
    [OrderHed].[ShipToNum] as [OrderHed_ShipToNum],
	[OrderHed].[PrcConNum] as [OrderHed_PrcConNum],
	[OrderHed].[ShpConNum] as [OrderHed_ShpConNum],
	[CustCnt].[Name] as [CustCnt_Name],
	[CustCnt].[EMailAddress] as [CustCnt_EMailAddress]
from Erp.OrderHed as OrderHed
inner join Erp.CustCnt as CustCnt on 
	OrderHed.Company = CustCnt.Company
	and OrderHed.CustNum = CustCnt.CustNum
	and OrderHed.ShipToNum = CustCnt.ShipToNum
	and OrderHed.PrcConNum = CustCnt.ConNum

image

The Sold To contacts are located here on the Customer record. If you do not have any contact records here, they will not show up on the Order.

image

1 Like

Thanks for your help. Greatly appreciated.

I did a trace on the GetList method on the Sales Order screen. The query in the trace showed ShipToNum=“” So, i changed my query to:
select
[CustCnt].[CustNum] as [CustCnt_CustNum],
[CustCnt].[ConNum] as [CustCnt_ConNum],
[OrderHed].[ShipToNum] as [OrderHed_ShipToNum],
[CustCnt].[ShipToNum] as [CustCnt_ShipToNum],
[CustCnt].[Name] as [CustCnt_Name]
from Erp.OrderHed as OrderHed
inner join Erp.CustCnt as CustCnt on
OrderHed.CustNum = CustCnt.CustNum
and OrderHed.PrcConNum = CustCnt.ConNum
and ( CustCnt.ShipToNum = ‘“”’ )

where (OrderHed.OrderNum = 1344)

Realized the other record being displayed was the Primary Ship To Contact. The CustCnt.ConNum for that row was 1 as well.

Sanity check! Thanks

That is a bandaid fix but not a real solution.

This solution will fail as soon as you do have a value in the custcnt.shiptonum field.

I just feel that the way the database is designed for custcnt is quite clunky. Company, ShipTo, CustID and ConNum are the primary keys, where it would have been much easier for me if only ConNum was the PK and unique for each entry.

In my environment, the sales staff haven’t setup the ship to addresses correctly. Moreover, the ship to address on the sales order may be different from the address stored in the contact person’s ID. So a join including Ship To leads to nothing.

Does anyone have a solution for this?

That being said, I don’t understand how it’s working in Epicor, since the UI is loading the correct field. How does the UI identify that it’s correct?

This is my query:

select 
       [OrderHed].[OrderNum] as [OrderHed_OrderNum],
       [OrderHed].[PONum] as [OrderHed_PONum],
       [OrderDtl].[POLine] as [OrderDtl_POLine],
       [CustCnt].[Name] as [CustCnt_Name],
       [CustCnt].[City] as [CustCnt_City],
       [OrderDtl].[XPartNum] as [OrderDtl_XPartNum],
       [OrderDtl].[PartNum] as [OrderDtl_PartNum],
       [OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
       [OrderDtl].[SellingQuantity] as [OrderDtl_SellingQuantity],
       [OrderDtl].[SalesUM] as [OrderDtl_SalesUM],
       [OrderHed].[OrderDate] as [OrderHed_OrderDate],
       [OrderDtl].[RequestDate] as [OrderDtl_RequestDate],
       [OrderHed].[NeedByDate] as [OrderHed_NeedByDate]
from Erp.Customer as Customer
inner join Erp.OrderHed as OrderHed on 
       Customer.Company = OrderHed.Company
       and Customer.CustNum = OrderHed.BTCustNum
       and ( OrderHed.OpenOrder = 1)

inner join Erp.OrderDtl as OrderDtl on 
       OrderHed.Company = OrderDtl.Company
       and OrderHed.OrderNum = OrderDtl.OrderNum
       and ( OrderDtl.OpenLine = 1)

inner join Erp.CustCnt as CustCnt on 
       OrderHed.Company = CustCnt.Company
       and OrderHed.CustNum = CustCnt.CustNum
       and OrderHed.PrcConNum = CustCnt.ConNum
where (Customer.CustID = 'P_ERGON') AND OrderHed.OrderNum=2352576
ORDER BY OrderHed_OrderNum;

Issue:

ContactID will reset if a ship to num is there.

Therefore you can have entry as follows:
1,2,0,1;
1,2,0,2;
1,2,1,1;

Currently the join between Order Header and Customer Contact is on Company, Customer ID and Contact ID, I am getting duplicates.

So, Because we are joining on the first second and fourth column, we get duplicates in the first entry and the third entry.

Not at my computer, so I would have to double check tomorrow morning.

I think you need to join on Customer.CustNum = OrderHed.CustNum. And not on BTCustNum. CustNum is the “base” customer number on all records. The bill to can be a completely different customer all together.

I read it again. There are two different levels of contacts on the customer. The sold to and the bill to has one level and the ship to is the other level. When you need a sold to or bill to contact, you join on company, custnum, and connum. In this instance, the ship to will always be empty. You only join on ship to when needing a ship to contract.