I’m curious how I can create a query with OTS.State + the actual ShipTo.State location correctly.
The tables that are needed are, InvcHead (amounts and balances), Customer, OrderHed (OTS stuff), ShipHead, ShipDtl (??), and ShipTo.
How can I connect all of these together without getting 7,000,000 rows? It doesn’t seem like the Shipping tables connect to Order/Invc tables very nicely… Am I missing something?
Essentially what I’d like to see is the Sales totals for each ship to state… The problem is we use OTS for some of our orders (OrderHed.OTSAddress1, etc.) and then we use the customer level ship to information (Customer.Address1, etc.), and we also use the Customer ShipTo’s as well (ShipTo.Address1, etc. - I believe). These don’t seem to play nicely together
Answer: Dont look at the sales order.
Start with a query on the INVOICE DETAIL and link back to the Ship Header (always a 1 to 1 link)… the ship header should show the shipping address. But the invoice header may also show it.
What I have had to do in this case is to create a calculated field to first look for the OTS checkbox first, if that is false look at the ship to num field, if that is null, then default to the sold to address.
Tim is right that sales should start with the invoice tables, but I found it best to then get back to the order header and do the calculated ship to field. You may need multiple calculation for each ship to detail field you need.
If this is for tax reporting, Epicor wrote a really cool report: Sales Tax. It reports everything by Tax Code or Tax Region. If one creates the tax entries (even for zero rates) and maintains them on the Customers/Ship-Tos/Order Rels, then this becomes a much easier report. People avoid doing it everywhere I’ve worked for some reason but once done, it’s beautiful.
True. So we need OrderHed.OTSstate plus any ShipTo.State info as well… When I bring in the ShipTo table it blows up my query. As far as I know, this is the only table that contains the actual ShipTo State from the pack. If I wanted to just display the invoice total (DocInvoiceBal or whatever the field is) and where the pack actually shipped then I get a bunch of rows (somewhere in the millions) - I’ll keep messing with it.
Also here’s my query so far - (tons of duplicate rows for each invoice/pack…) -
select
[InvcDtl].[InvoiceNum] as [InvcDtl_InvoiceNum],
[InvcDtl].[InvoiceLine] as [InvcDtl_InvoiceLine],
[ShipTo].[State] as [ShipTo_State],
[Customer].[State] as [Customer_State],
[InvcHead].[Posted] as [InvcHead_Posted],
[InvcHead].[DocInvoiceBal] as [InvcHead_DocInvoiceBal],
[InvcHead].[DocInvoiceAmt] as [InvcHead_DocInvoiceAmt],
(case
when InvcDtl.UseOTS = ‘1’ then InvcDtl.OTSState
when InvcDtl.ShipToCustNum > ‘’ then ShipTo.State
else Customer.State
end) as [Calculated_TheShipTo]
from Erp.InvcDtl as InvcDtl
left outer join Erp.ShipTo as ShipTo on
InvcDtl.Company = ShipTo.Company
And
InvcDtl.CustNum = ShipTo.CustNum
And
InvcDtl.ShipToNum = ShipTo.ShipToNum
inner join Erp.Customer as Customer on
InvcDtl.Company = Customer.Company
And
InvcDtl.CustNum = Customer.CustNum
inner join Erp.InvcHead as InvcHead on
Customer.Company = InvcHead.Company
And
Customer.CustNum = InvcHead.CustNum
and ( InvcHead.Posted = 1 )
This should be Invcdtl.shiptonum (not shiptocustnum)
when InvcDtl.UseOTS = ‘1’ then InvcDtl.OTSState
when InvcDtl.ShipToCustNum > ‘’ then ShipTo.State
else Customer.State
end) as [Calculated_TheShipTo]
from Erp.InvcDtl as InvcDtl
left outer join Erp.ShipTo as ShipTo on
InvcDtl.Company = ShipTo.Company
And
InvcDtl.CustNum = ShipTo.CustNum
And
InvcDtl.ShipToNum = ShipTo.ShipToNum
inner join Erp.Customer as Customer on
InvcDtl.Company = Customer.Company
And
InvcDtl.CustNum = Customer.CustNum
inner join Erp.InvcHead as InvcHead on
Customer.Company = InvcHead.Company
And
Customer.CustNum = InvcHead.CustNum
and ( InvcHead.Posted = 1 )
