BAQ for Parts that have no purchase history at current revision

Epicor ERP 10
,
1

I’m working on creating a BAQ for Parts that have no purchase history at the current revision level. For us, when a part is revised, it will increase our lead time. Our purchasing department would like to have visibility on these parts before they submit a PO. They can find their answer with other menu’s but I would like to create this in a dashboard and narrow it down to some specific part classes.

Currently I have a Query1 (Union), Query2(Union), and a subquery.

TopLevel query has PO Detail and SQ
image

Since its a union the next subquery is the same.

The subquery used by the union grabs the latest revision level from a calculated field.

image
image

Here is my theory on how it should work.

Query 1 (union)
Retrieve all part numbers with latest revision that have had a PO in the past

Query 2 (union)
Retreive all part numbers from system with latest revision

By comparing these two I should be left over with only parts that have not been placed on a purchase order in the past, but I also have to determine this by revision level. Revision level is a field in PODetail table, so thats good.

Any advice?

2

Here is the BAQ if you want to check it out. It’s somewhat functional, I just have some false positives.
I have criteria of class # 035 to limit my results, you may want to remove that.

NeverPurchased.baq (44.5 KB)

3

You could simply make a subquery to joining POHeader and PODetail, returning the columns:

  • PODetail_PartNum (GroupBy)
  • PODetail_RevisionNum (GroupBy)
  • Calculated_LastOrdDate (expression of MAX(POHeader.OrderDate)

Then the top level query (subquery1) would look like:

image

image
image884×160 4.54 KB

displaying fields:
image

now that will show every part and revision whether it was purchased or not. The last time that Part/Rev was purchased will be shown on the last column, and be blank if never purchased before.
image

If you only want to see the ones that have never been purchased, add a Subquery criteria of:

image
image918×349 18.1 KB

4

Nice. I’ll give that a try right now and let you know.

5

Looks good! You make it seem so easy haha. Thanks!

PartNotOrdered.baq (33.7 KB)