I am making a BAQ to look at some orders, with the billing address and the ship to address of that order.
My BAQ is pulling the incorrect ship to in: one of the ones available, but not the one we had selected for that order. Obviously, I am missing the correct connection. Can anyone help me, please?
Thank you.
select
[OrderHed].[Plant] as [OrderHed_Plant],
[Customer].[CustID] as [Customer_CustID],
[OrderHed].[CustNum] as [OrderHed_CustNum],
[Customer].[Name] as [Customer_Name],
[Customer].[Address1] as [Customer_Address1],
[Customer].[Address2] as [Customer_Address2],
[Customer].[Address3] as [Customer_Address3],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[PONum] as [OrderHed_PONum],
[OrderHed].[OrderDate] as [OrderHed_OrderDate],
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[UnitPrice] as [OrderDtl_UnitPrice],
[ShipTo].[ShipToNum] as [ShipTo_ShipToNum],
[ShipTo].[Name] as [ShipTo_Name],
[ShipTo].[Address1] as [ShipTo_Address1],
[ShipTo].[Address2] as [ShipTo_Address2],
[ShipTo].[Address3] as [ShipTo_Address3],
[ShipTo].[City] as [ShipTo_City],
[ShipTo].[State] as [ShipTo_State],
[ShipTo].[ZIP] as [ShipTo_ZIP],
[ShipTo].[Country] as [ShipTo_Country],
[Customer].[CreditHold] as [Customer_CreditHold],
[OrderHed].[TermsCode] as [OrderHed_TermsCode],
[ShipTo].[PhoneNum] as [ShipTo_PhoneNum],
[Customer].[PhoneNum] as [Customer_PhoneNum],
[Customer].[EMailAddress] as [Customer_EMailAddress],
[ShipTo].[EMailAddress] as [ShipTo_EMailAddress]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
inner join Erp.Customer as Customer on
OrderDtl.Company = Customer.Company
and OrderDtl.CustNum = Customer.CustNum
inner join Erp.ShipTo as ShipTo on
Customer.Company = ShipTo.Company
and Customer.CustNum = ShipTo.CustNum
and Customer.ShipToNum = ShipTo.ShipToNum
where (OrderHed.Plant = 'FSUSA' and not OrderHed.CustNum = 598 and OrderHed.OrderDate >= @StartDate and OrderHed.OrderDate <= @EndDate)
Yup…so you have to check OTS, change the OrderHed/ShipTo an outer join…and even then it might not be right if someone changes the address in the ShipTo record. Good times.
What everyone said about OTS plus know that the order release table is where jobs, shipments, invoices, etc are linked not the order head table. OrderRel also has a UseOTS box that can be used.
If you’re looking at already shipped orders you can perhaps go from the ShipDto to ShipHead table to get the data as a PackID can only be shipped to one address.
Depends on your company and if you guys utilize multiple releases. A previous company used one order for a blanket-order and some had over a thousand releases per line to ship to all the locations. Where I am now we’ve only done it a few times but I just keep with the habit of going off the OrderRel table.
Yes, OrderRel also has a OTS capability separate from Order Head.
I edited my post to delete the part where I said something that you already said.
But right, where I am we rarely, if ever, get that clever with releases and all that. So it should be fine.
I take my cue from Purchase Advisor, where it uses the receipt header date, as if that’s just as good as the date on the actual receipt line. “I gave you a date; what more do you want? Accuracy?!”
Okay… I am confused, I am sorry. I tried to pull the OTS in but I got nothing from it. We have a Customer and then just add a bunch of Ship To locations. I guess I am uncertain how to fix it exactly or what I should do instead. I apologize.