Right tool to use for task? BAQ, Report, stored procedure?

Epicor ERP 10
1

I have a request and am looking for a bit of help on which tool to use.

The request is to show the last 12 months, by using the 1st of the month as the date., what the price of parts were?
I can eye ball the data based on the PartTran transactions for a part. Having a bit of difficulty with the report.

I would like to create a list of dates and then do a lookup for the most recent partran transaction for the part before the list of dates. That way the partcost would still show for month were there are not transaction in.

ie.
PartTran transaction occurred on 1/3/18
2/1/18 would use the 1/3/18 transaction
3/1/18 would use the 1/3/18 transaction
PartTran transaction occurred on 3/3/18
4/1/18 would use the 4/3/18 transaction
and so forth.

This doesn’t need to be realtime, wondering if I should script a procedure to create a table with the data.

Writing a dynamic sql also might do this.

Any thoughts or has someone already done this. or have a trailing 12 month BAQ I can leverage for this. :slight_smile:

Thanks

Ken

2

If this will be an ongoing thing, use a UD table to hold a copy of each parts cost on the first of the month. Use Date and PartNum as the keys.

Create a process that runs at 12:01AM on the 2nd of each month, to add the cost to the UD table. This wouldn’t require there to be any PartTrans in a given month.

3

On your original question about getting the cost when there is no Part Trans in a month…

Have a sub query the returns the MtlUnitCost for the max TranNum, of records whose trandate is <= the date of the 2nd.

Even if now Part Trans happened in Feb, the last record (max TranNum) with a TransDate <= 2/1/2018 would be a record from January (or further back).

4

Here’s something i threw together. The query wouldn’t be very efficient but maybe you can get an idea from it.
declare @Date varchar(25)
Set @Date = ‘2018-09-01’
declare @Company varchar(25)
set @Company = ‘company’
select part.Partnum,
ISNULL((select mtlunitcost from erp.PartTran where PartTran.PartNum = part.PartNum and PartTran.Company = part.Company and PartTran.TranNum
= (select MAX(TranNum) from erp.parttran p where p.TranDate < DATEADD(m,-12,@Date) and p.PartNum = PartTran.PartNum and p.Company = PartTran.Company)), 0) as ‘-12’
from Part where Company = @Company

5

yes this will be ongoing.

6

thanks Dan. I will check on what this gets us. I think this question is part of a bigger issue; managing costs of purchased parts over the parts lifecycle. Just looking to get a head start on what Epicor can do.

7

One thing you need to know about E10 and Part Costs…

Changes in the part cost can be retroactive. Meaning the latest part transaction for a given part may actually be for a date prior to earlier part transactions. And cost changes can be back dated.

For example, picture the following sequence

  1. SSR is run on 6/1 with a SSR date of 5/30. It shows the cost of part XYZ as $1.50
  2. A PO Receipt is entered on 6/2, but with a receipt date of 5/29. The Unit cost on the PO was $1.75.
  3. SSR is run on 6/3 with a SSR date of 5/30. It now shows the cost of part XYZ was $1.75 on 5/30.

I used LAST costing to keep the example simple.

One more thing, the QOH for those two SSR reports will be different