SQL Query Help

Epicor ERP 10
,
1

Hello All,

I’m not too experienced with SQL, but I wrote a query to display all of our orders for November in 2016.

I have these fields displayed/selected:

image
image.png922×269 10.3 KB

Here’s the query -

  • select
    [OrderHed].[OrderNum] as [OrderHed_OrderNum],
    [OrderHed].[OrderDate] as [OrderHed_OrderDate],
    [OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
    [OrderHed].[SalesRepList] as [OrderHed_SalesRepList],
    [OrderDtl].[SellingQuantity] as [OrderDtl_SellingQuantity],
    [OrderDtl].[UnitPrice] as [OrderDtl_UnitPrice],
    [OrderHed].[OrderAmt] as [OrderHed_OrderAmt],ProdCode,
    CASE WHEN [OrderDtl].OrderLine <= 1 THEN [OrderHed].OrderAmt else 0.00 end as Calc_OrderAmt

  • from Erp.OrderHed as OrderHed
    inner join Erp.OrderDtl as OrderDtl on
    OrderHed.Company = OrderDtl.Company
    And
    OrderHed.OrderNum = OrderDtl.OrderNum

    where [OrderHed].[OrderDate] > ‘2016-10-31’ and [OrderHed].[OrderDate] < ‘2016-12-01’

So my goal for this query is to be able to calculate the total OrderAmt - but as you can see there’s duplicate rows for orders that have more than one line so the total is off by quite a bit…

I tried “CASE WHEN [OrderDtl].OrderLine <= 1 THEN [OrderHed].OrderAmt else 0.00 end as Calc_OrderAmt” but that didn’t work since we have a few orders that don’t start with line 1 and those OrderAmts come in as 0.00. Is there a statement where it only grabs the one OrderAmt per order? Or will I have to build a different query with Invc tables and do something like OrderDtl.SellingQuantity * OrderDtl.UnitPrice + InvcMsc.MiscAmt ?

2

Why are you using SQL instead of a BAQ?

You’re going to want to get the pricing from the OrderDtl table and join it to the OrderMsc for the Order level Misc charges. If you need it, taxes are calculated at the Release level in OrderRelTax to throw a little wrench into your work. Also the OrderHed table could have order level Misc charges.

3

Try running this Code, it may help you.
SELECT SUM(OrderAmt) from erp.OrderHed where VoidOrder=0 and OrderDate between ‘2016-11-01’ and ‘2016-11-30’

Arun

4

Few ways to do this.

All depends on what you want your report to show. If it is just order and total, just use OrderHed, and don’t join the OrderDtl table. You will get one header row for each detail row with the order.

If you need OrderDtl information then you need to calculate each line from the OrderDtl by sellingQuantity * unitPrice. Then you can ignore the OrderHed.OrderAmt.

You also talk about invoice. Order and invoice dates might be in different months, just be careful with how you show the data there.

5

We can’t just do SellingQuantity * UnitPrice since there’s Misc charges on a lot of orders. (that’s what I meant by grab a few invoice tables… e.g., InvcHed, InvcMisc.)

We’re using SQL here instead of a BAQ because this query is for a SSRS report that a consultant is working on. We have PY Current Month, YTD, PYTD, etc. And the totals are all off because of the duplicate rows for line data. The problem is we want to see the line information, but we want the totals to be correct… One idea I had was to create a calculated field that basically said OrderAmt / TotalLines - then the total would be correct; but the TotalLines information is incorrect for some orders.

Now I need to figure out how to make a calculated field that counts the amount of lines per order. Then either DMT the correct results into the TotalLines field, or just use the calculated field

edit: Nevermind - I think the TotalLines field is actually showing correct results… Excel screwed me over

6

So I decided to go with a calculated field that says “[OrderHed].[OrderAmt] / [OrderHed].[TotalLines] as LineAmt”

We’re probably going to keep this field hidden and then only calculate the totals off of it. That way users can still see line detail, order amounts, and the correct totals but they won’t see this “fake” line amount…

7

select oh.OrderNum,
oh.OrderDate,
c.CustID,
c.Name,
oh.DocOrderAmt,
(select sum(ExtPriceDtl) from Erp.OrderDtl od where oh.Company = od.Company and oh.OrderNum = od.OrderNum and od.KitFlag <> ‘P’) as SumOrderDtl ,
(select sum(TaxAmt) from Erp.OrderRelTax ot where oh.Company = ot.Company and oh.OrderNum = ot.OrderNum) as SumOrderTax,
(select sum(MiscAmt) from Erp.OrderMsc om where oh.Company = om.Company and oh.OrderNum = om.OrderNum) as SumOrderMsc
from Erp.OrderHed oh inner join Erp.Customer c on oh.Company = c.Company and oh.CustNum = c.CustNum
order by oh.OrderNum

8

Just thought, check the od.KitFlag bit. We use Sales Kits, and having the Kit price and individual components causes a double up.

Our OrderAmt field on OrderHed is out of wack for many thousands of orders in our system. I raised with support, but didn’t get very far…!