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.