This definition allows the same ConNum to be used for more than one Customer contact, as long as the ShipToNum is different for each.
To me this is goofy because I don’t see why you’d want to use the same ConNum across multiple contacts for the same customer.
I’m running into an issue where a routing rule (APR) sends invoices to wrong contact for a customer. As best I can determine, the flaw in the logic is as follows:
We have a CustCnt record designated as Primary Billing with ConNum = 1 and name ‘INVOICES’.
We have another record with same ConNum and name ‘John S’.
When attempting to send an email, the logic identifies ‘INVOICES’ as primary billing and notes ConNum = 1. It then searches for the email address corresponding to ConNum = 1 … which in our case yields two addresses. Presumably the routing rule grabs the address of the first record … but the correct address belongs to the second record.
We have seen this happen at least twice in the past few months. I can think of a couple possible workarounds but wanted to see if anyone knows the ‘best’ way to adress. My two options are:
Inactivate the incorrect CustCnt record with that ConNum, and then add that contact a second time so that it’s assigned a new ConNum.
Modify the APR routing rule to also utilize a second field with some distinguishing value, such as Func = INVOICES.
@tkueppers If you look at the data the primary billing, purchasing and shipping are in the data with blank shiptonum and the customer record also has a blank shiptonum so if you include customer.shiptonum=CustCnt.ShipToNum in your join you should only get one record.
I tested our data with the select below and only got one per.
Greg
select cc.Name,c.PrimBCon ,c.*
from Customer c
left outer join CustCnt cc on c.CustNum=cc.CustNum and c.PrimBCon = cc.ConNum and cc.ShipToNum =c.ShipToNum
where c.PrimBCon <> 0
@gpayne where do I specify a query/criteria in an APR routing rule. When creating the template for the associated email, I can filter by a single field, but I don’t see means to do a join.
@tkueppers Hmm, I don’t know. We use the Calc_CustEmailAddress which is set with epiMagic to the right address. I didn’t make it. We had Epicor do our first SSRS and APR forms.
We encountered the same issue and what we did is to create a UD column with null value in the InvcHead table. Then we change the Report Data Definition of that report style to have a relationship match the new UD column created and the CustCnt.ShipToNum.
I’m trying to create an open sales order report where they need the sold to person’s name in the report as well.
I am running into the issue where you have multiple contact persons with the same contactnum and as a result i’m getting duplicates.
most of the ship to fields in the custcnt table is blank so I cant really do the join on shiptonum as suggested in an earlier post because that join is also giving erroneous results.
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 = true )
inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
and ( OrderDtl.OpenLine = true )
inner join Erp.CustCnt as CustCnt on
OrderHed.Company = CustCnt.Company
and OrderHed.PrcConNum = CustCnt.ConNum
and OrderHed.CustNum = CustCnt.CustNum
where (Customer.CustID = @CustomerID)
This query is currently giving us duplicates because of the join on OrderHed and CustCnt
I cannot add a join on ShipToNum as well because the ShipToNum is blank for a lot of customer contact entries.
This query is returning duplicates since when sales were setting up the customer contact details they may or may not have filled the shiptonum field and because this field is blank I can’t really create a meaningful join. Hope that makes sense.
We can’t use the ShipToNum as blank because that won’t uniquely identify the contact person on that particular sales order.
There must be some logic in Epicor through which a sales order is binding to the Attn To: field since it works on the Order Entry UI, we need to find what that logic is and apply the same logic to our query.