I’d like to create a BAQ with last cost, the date received and the part number. Can someone direct this endeavor? I’ve search past topics and didn’t see it addressed specifically like this. We use standard cost, if that matters in this case.
Are you only looking for purchased parts? Do you only want to see changes in parts received - not active?
If so, you could use one of @banderson’s posts about Window Functions in your search query.
I’m going to ask some exploratory questions before I answer.
Can you get the answer the way you are looking for it from the UI currently?
I want to know 1. if this is a stock Epicor number that you are trying to get, and 2. you know what you are looking for.
The reason I ask that, is there have been a lot of posts lately where the people helping are assuming a level of knowledge and tech ability that doesn’t match the person who asked the question.
So what have you tried so far? Explain what you are trying to emulate, and we can help you where you are stuck.
I would like a list of the last cost and the last date received.
I ran a “receiving” baq with all of the parts received through Receipt Entry and tried to back through it. We purchase mostly from overseas and I’d hoped to “go back in time” to get the last cost/date information. That worked for the most part except where there were parts that were purchased several times through the year and it did not grab the last date information.
I see the last cost is available by looking in Material Management>Inventory Management>General Operations>Cost Adjustment, but there is no date associated with that location.
It looks like someone else had a similar question "I must do a BAQ that shows me the last 6 costs" Nov '2021. Doug.C provided a BAQ to help that person, but he a calculation column in the CTE and that is above my tech ability. Otherwise, I would try to modify it to the last ONE cost and the run a list by PO number and Order date and do a vlookup to get the date.
So the long answer is I don’t see a place readily in Epicor that shows the Last Cost and the date of that purchase. …it’s been a long day though.
One method to consider:
Last Cost is a straight pull from PartCost
Also pull in PartTran with the table criteria of TranType = PUR-STK.
Link the two by the standard table relations of Company = Company & PartNum = PartNum.
Generate a calculated field of the date type as:
max(PartTran.TranDate)
This will require you to mark the other displayed fields (PartNum & LastMaterialCost) as group by, but it’s a quick solution.
You could use just those two tables, but this has potential to also pull any normally manufactured parts that were purchased instead. Linking the Part table with a table criteria of TypeCode = P would resolve that if it’s a concern.
Note this will only pull parts that have both a last cost and purchase to stock transaction. If one is somehow missing, then the part will not be included. I’m told Last Cost is only updated when an item is moved to stock, so anything purchased direct to a job or sales order would not update the last cost or have a PUR-STK transaction.
For “last transactions” type queries, I have a method I use which does not require subqueries. Others have subquery methods and those work just as well. I start with two copies of the table dragged onto the design field, in this case RcvDtl. For the “phantom” next transaction, I will change the name of the table to NextRcvDtl.
When you join the tables together, Company and PartNum have to match, and some field that indicates sequence, needs to be greater in the “next” table than in the “current” table. I don’t like to use dates because if you receive the same part twice in one day, it could cause an error in the query. If that’s not an issue, the method shown here should work. Change the join to Left Join also called “all rows from RcvDtl.”
Add a subquery criterion to require NextRcvDtl.Company to be ISNULL.
Add your display fields, all from RcvDtl.
Hit the Test button and spot check the results against Part Advisor or Receipt Tracker. This should provide a more believable set of information than just looking in PartCost, because you want to know when it was received, and what the cost was for that specific transaction.
How It Works
The left-join tells Epicor to find the current receipt and if there’s a next one, find that receipt as well. Then your subquery criterion tells Epicor to show only receipts for which there is no next one found.
4 Likes
This seems to work very well. Thank you so much! I did have to switch the operator around on the Receipt date though. Have a great day!