Is there a DB Table/Field that displays the last price of a part that a part was sold for?
Right now, we are using some calculated fields, - avg( OrderDtl.UnitPrice) * PartWhse.OnHandQty -,
to determine the average unit price in order to examine the approximate value of our On Hand finished goods. Because this BAQ is also pulling only open orders, it has resulted in some blank fields under a large number of on hand finished parts.
We would rather pull the - last price a unit sold * PartWhse.OnHandQty - (For all orders, open or closed)
in order to more accurately determine the approximate on hand value of these finished goods.
Anyone have any suggestions on how we could accomplish this?
Thanks Adam!
Would you know which Table would contain the best Display Fields to incorporate into my Main and inner Subqueries to best determine the last selling price of a part?
(Just a heads up, I’ve only been using Epicor for less than a month, and the person attempting to show me some things is self-taught, does not have much time to teach me anything, and isn’t the best when he does teach)
I am using the PartNum and also Table criteria of OrderDtl.PartNum and OpenLine = True from the OrderDtl table within the 2nd subquery.
But because 50% of these finished goods do not have an average unit price associated with them, we would rather use different fields and/or Table criteria to determine the last price that all of our On Hand finished parts sold for
Back to using orderdtl then.
Do you have parts that are on multiple lines on an order? I am assuming no.
The rest of this is what the link above is doing for the PO tables.
First subquery
get the latest order by part
select partnum, max(ordernum) as maxOrder
from erp.OrderDtl
group by PartNum
That will give you a list of parts and the latest (last) ordernum it was on.
Then you join that table to the Erp.OrderDtl ( I know it is the same table you just used, but it is needed to get the price).
With the joined table you will then be able to get
OrderNum, PartNum, Last Order Price
Now you can join this subquery table to the PartWhse table to do your calculation.
The baq will have three querys in the end
inner subquery1 (MaxOrderforPart)
inner subquery2 (PriceofPartforMaxOrder using Subquery1)
top query (PartWhse and Subquery2) to do calculations.
@mdiemer welcome to Epicor! As looks to be the case here, learning Epicor’s data structure and understanding what your company’s data “looks like” is almost always the hard part of any BAQ. Syntax, subqueries, calculated fields, etc. may seem tricky at first, but they are easy once you do a few and get the hang of it. But data structure takes a long time to learn. You’ll have to get a feel for how all the common Epicor tables are related to each other, and then get a feel for the character of your company’s data. One good way of learning Epicor structure is opening up all the commonly used menus, going to Field Help -> Technical Details, and read up / poke around to understand the relationship between the UI and the database. The best way to get a feel for your company’s data character is by learning as much as you can about every business process / how things are set up. Once you know what users are doing you’ll know what to expect your data to look like.
For your task there’s obviously no objectively correct way to do this, but here’s some thoughts on how to get to a “reasonable” figure
Base this off the InvcDtl table, not OrderDtl. That will ensure you’re pulling orders that were actually sold/shipped. Typically “Sales Reporting” should be done off the invoice tables, unless you have reason to use the GL tables (which I always try to avoid like the plague). I only base off the GL if Finance absolutely demands it.
Suggest taking some kind of average over the last X invoices for each part, or average of invoices for each part during current date minus X days. You don’t want the figure to be way off because a salesperson priced the last sale of a part way outside the norm.
If you do an average, it would be even better to do a weighted average taking into account the qty of the part on each invoice line. A simple average of Unit Price would skew the figures high (assuming Unit Price goes down the higher the qty).
Watch out for line discounts, make sure to handle them properly however you do this.
Awesome, thanks for your help and patience Ken!
I was able to produce some promising results.
There are some discrepancies that I’ll have to play with and modify but this has been very helpful.
@TomAlexander,
You’re right, understanding the data structure here is what is most challenging, and seems like it will take a good amount of time to really learn and understand. I did find great value in your suggestions, especially poking around in the commonly used menus with the technical details tab open to understand the UI and DB relationship. People here are very busy, so learning about their individual roles and how they use Epicor has been difficult. Especially since I come from a completely different industry that is not relative to manufacturing. Overall, I’ve been taking it day by day and have been making good progress in making my way into the ebb and flow of things. Thank you for taking the time to provide these encouraging and wise words.