BAQ How to get the Sales Order with Oldest Date

Epicor ERP 10
,
1

Hello, I need someones help. I am not sure what I am missing here.

I have been asked to create a BAQ containing the following:

  1. List of Part Numbers on Hold
  2. Type of Hold
  3. ECR Number
  4. Only the Latest Open Sales Order Number per part
  5. Latest Open Sales Order Date per part
    Example: Order 1 for part A was placed 6/01/2020. Order 2 for part A was placed 6/20/2020. I only want the Sales Order that was entered first which would be Order 1.

I have attempted to use the calculation field and used Min(OrderDate), however I still return multiple Sales order for each part. I only want one, the oldest sales order date per part number.
Here is what I have tried.

I created a subquery shown below:

image
image888×367 14.6 KB

The fields I pulled for the Subquery:
image

Then I created an Top Level Query, per shown below:

image
image718×297 9.56 KB

The fields I pulled for the Top Level:
image

The Calculated fields I tried to make:
image
image

My failed results where the highlighted lines would be what I want displayed out of the group of results. Also not sure how to handle different orders for the same part that have the same date (Highlighted in Green):

image
image754×404 12.3 KB

Thank you ahead of time!

2

Sort the subquery by OrderDate ascending and pick the Top 1 only.

1 Like
3

How do you tell the query to pick the top one only??

4

image

5

Wow… Duh Bekka. Haha Thank you Jamie Bassett! :slight_smile: :blush:

6

This is the first time I’ve seen a question on this board that I actually knew the answer to, so thank you for the monday morning pick me up! lol

1 Like
7

Still need HELP!!
I am still running into issues. If I use the Top function, then I only return one row of data. I thought I needed to separate it all out into independent sub-queries, but that is still not working. Any ideas how to get this to work?

Here is what I have. I have made 2 sub-queries and a top level query, as shown below.

image
image1036×111 5.04 KB

I have tried to use the “Top” and “Distinct Top” on both the ‘Order Details’ sub-query and the top query. When applied to the Sub-query ‘Order Details’ it returns only 1 row. When applied to the ‘Part and Order Detail’ top query, it returns 0 rows.

Sub-Query 1.) Parts on Hold Details
SubQuery Options:
image
Phase Build:

image
image889×315 16.6 KB

Display Fields:
image
image763×324 15.2 KB

Calculated Fields:
Total Demand
image
image796×362 17.4 KB

On-Hand Qty
image
image778×322 16.2 KB

Sub-Query 2.) Order Details
SubQuery Options:
image
Phase Build:
image
Display Fields:

image
image795×269 11.8 KB

Top Query 3.) Part and Order Details
SubQuery Options:

image
image695×381 10.4 KB

I have tried with Ties and without. Also Top and Distinct top.

Phase Build:
image
Display Fields:

image
image793×389 18.4 KB

Everything I have tried, only returns this one row…

image
image825×518 14.7 KB

Please help me figure out what I am doing wrong… If you need more information, please let me know. I am happy to supply it.

8

Before I get to far into this… Any open order that called out the part? Or just open orders where that part is on an open line?

9

Try this query to start:

  1. Join OrderDtl and OrderHed.
  2. Add criterion to OrderDtl: OpenLine = 1
  3. Create two calculated fields:
    (1) MinOrderDate = Min (OrderHed.OrderDate)
    (2) MinOrderNum = Min (OrderHed.OrderNum)
  4. Display these fields
    OrderDtl.PartNum (GROUP BY THIS)
    MinOrderDate
    MinOrderNum

This should give you one open row per part containing the part number, the earliest order number and the date of the earliest order number.

You can then join the results of this query to the Part table to pull in the rest of the data.

(My assumption is that you want open lines, and not any open order containing this part if the line itself is not open)

10

Not sure that would handle the fact that the order dates and order numbers aren’t always sequential.

For example, two open orders for P/N WIDGET-001

  1. Order 1000 was started on 6/1/20, (which defaults the order date to 6/1/20).
  2. Order 1005 was started on 6/8/20, (which defaults the order date to 6/8/20).
  3. Order 1000 has it’s order changed to 6/10/20.

Order 1005 is “older” as its OrderDate (6/8) is < 1000’s order date of 6/10.

Would your query show

PartNumber   MinOrderDate   MinOrderNum
==========   ============   ===========
WIDGET-001       6/8/2020          1000

Which are mismatched date and order numbers

11

You are right. Was not taking into account changed order dates, as we do not change them.

I guess you could decide to start either with oldest date or with smallest number and then bring in the other. Build it up, bit by bit.

12

You might have to combine the date and Ordernumber into a string so you first have something like:

image

Then on another subquery, select the MIN(OrdeDateNum) to get the oldest order order for that part

image

Then another level of sub-query to extract the Ordenum from the OrderDateNum string

13

This should get you started:

SubQuery1 (InnerSubQry)

image
Table Criteria: OrderDtl.OpenLine = 1

Display Fields:

  1. OrderDtl_PartNum (GROUPED BY)
  2. Calculated_OrderDateNum (GROUPED BY)
    = convert(varchar, (Datepart(Year,OrderHed.OrderDate)*10000+datepart(Month,OrderHed.OrderDate)*100+datepart(Day,OrderHed.OrderDate))) + '-'+ convert(varchar, OrderHed.OrderNum)

SubQuery2 (InnerSubQry)

image

Display Fields:

  1. OrderDtl_PartNum (GROUPED BY)
  2. Calculated_OldestOrderNum
    = Min(SubQuery1.Calculated_OrderDateNum)

SubQuery3 (TopLevel)

image

Display Fields:
image

  1. OrderDtl_Partnum
  2. Calculated_OldestOpenOrderDate
    = convert(date,substring(SubQuery2.Calculated_OldestOrderNum,1,8))
  3. Calculated_OldestOpenOrderNum
    = convert(int, Substring(SubQuery2.Calculated_OldestOrderNum,10,10))

That will get a table on your top level that has one row per part number with the OrderNumber for the order with the oldest order date,

image

2 Likes
14

So it needs to be any part number that is on Eng hold AND has an open order against it. It can be a single stand alone order or one line on a multiple part order. Does that make sense?

15

Yes. What I’ve outlined above will give you a list of all the parts that are on an open order line, showing the order number with the oldest Order Date.

From there you can add you other tables to relate to the PartNum or the OderNum.

16

This might be kind of dirty but here is an alternate option. It will do a row count and sort by ASC or DESC. Then you can choose to only keep row #1.

On your subquery create calculated field.
ROW_NUMBER() OVER(PARTITION BY OrderDtl.PartNum ORDER BY OrderHed.OrderDate DESC)

On your Top Query, set your table criteria for the SubQuery to = constant of 1.

1 Like