I'm also having trouble finding the average cost. I think I even found problems in the way Epicor (9.05.604A) calculates the average, when there are, for example, three PUR-STK's transactions in a row with the same cost, but still testing...
Anyway, here's what I've got so far (i'm taking a SQL approach):
--GET THE AVERAGE COST OF 10EL35010120 PART FOR EVERY PARTTRAN
select PT.TranDate, PT.SysTime, PT.TranNum, PT.Plant, PT.PartNum, PT.TranType, PT.TranQty, PT.MtlUnitCost
, SUM(PT2.TranQty * dbo.A_TranTypeValue(PT2.TranType)) as QTYACUM
, SUM(PT2.ExtCost * dbo.A_TranTypeValue(PT2.TranType)) as COSTACUM
, case when SUM(PT2.TranQty * dbo.A_TranTypeValue(PT2.TranType)) = '0'
then '0' else round(round(SUM(PT2.ExtCost * dbo.A_TranTypeValue(PT2.TranType)),1)
/ SUM(PT2.TranQty * dbo.A_TranTypeValue(PT2.TranType)),2) end AVERAGE
from PartTran PT
inner join PartTran PT2 on PT.Company = PT2.Company and PT.PartNum = PT2.PartNum and PT2.CostMethod='A'
and (PT.TranDate > PT2.TranDate or (PT.TranDate = PT2.TranDate and PT.TranNum >= PT2.TranNum))
where PT.Company = 'epic11' and PT.PartNum = '10EL35010120'
group by PT.TranDate, PT.SysTime, PT.TranNum, PT.Plant, PT.PartNum, PT.TranType, PT.TranQty, PT.MtlUnitCost
order by PT.TranDate desc, PT.SysTime desc, PT.TranNum desc
And the dbo.A_TranTypeValue function body (should be extended for every TranType):
-- Add the T-SQL statements to compute the return value here
SELECT @TranTypeValue = case @TranType
when 'ADJ-CST' then '0' when 'PUR-INS' then '0'
when 'ADJ-PUR' then '1' when 'PUR-STK' then '1'
when 'ADJ-QTY' then '1' when 'PUR-UKN' then '1'
when 'DMR-REJ' then '-1' when 'RMA-INS' then '0'
when 'DRP-CUS' then '-1' when 'STK-CUS' then '-1'
when 'INS-DMR' then '0' when 'STK-INS' then '0'
when 'INS-STK' then '1' when 'STK-PLT' then '-1'
when 'PLT-STK' then '1' when 'STK-STK' then '1'
when 'PUR-DRP' then '1' when 'STK-UKN' then '-1'
else '0' end
-- Return the result of the function
RETURN @TranTypeValue
---In vantage@yahoogroups.com, <cathy@...> wrote:The Transaction History can be misleading as far as what the average cost is on a certain day.
Example - Average cost can be .50 and you can have a PO that is received were the cost is .35. Let say the new average is now .42.
The system will establish a new average but when looking at the PO - PUR-STK - the transaction it will show the .35 from the PO for the average mtl cost.
It does this with manufactured parts too.
--- In vantage@yahoogroups.com, Jose Gomez <jose@...> wrote:
>
> Yeah Calvin the problem is that we need the AVG cost at that point in time.
>
>
> *Jose C Gomez*
> *Software Engineer*
> *
> *
> *
> *T: 904.469.1524 mobile
> E: jose@...
> http://www.josecgomez.com
> <http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
> <http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
> <http://www.josecgomez.com/professional-resume/>
> <http://www.josecgomez.com/feed/>
> <http://www.usdoingstuff.com>
>
> *Quis custodiet ipsos custodes?*
>
>
> On Fri, Aug 16, 2013 at 5:25 PM, Calvin Dekker <calvind@...>wrote:
>
> > **
> >
> >
> > Jose -
> >
> > The PartTran record should have the specific cost that was associated with
> > the transaction. The cost fields in the PartTran table are based on the
> > date the transaction took place. Would this work for you?
> >
> > Calvin Dekker
> > [cid:image001.jpg@...]<http://www.codabears.com/>
> > 630-672-7688 x1484
> >
> > *Solutions for Epicor users.
> >
> > "No trees were harmed during the sending of this message, however a large
> > number of electrons were terribly inconvenienced."
> >
> > From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> > Of Jose Gomez
> > Sent: Friday, August 16, 2013 4:03 PM
> > To: Vantage
> > Subject: [Vantage] Cost for a specific date (historic)
> >
> >
> > Does anyone know or have a query that will return historic cost from Part
> > Tran as of TranDate?
> > I've poked and changed and ran stuff but I can't quite get it, here is what
> > I have so dar. Any ideas? the numbers are off (some slighly some
> > completely)
> >
> > SELECT Company, PartNum,
> >
> > SUM(mtlunitcost)/ISNULL(NULLIF(SUM(CASE WHEN mtlunitcost > 0 THEN 1 ELSE 0
> > END),0),1),
> >
> > SUM(burunitcost)/ISNULL(NULLIF(SUM(CASE WHEN burunitcost > 0 THEN 1 ELSE 0
> > END),0),1),
> >
> > SUM(subunitcost)/ISNULL(NULLIF(SUM(CASE WHEN subunitcost > 0 THEN 1 ELSE 0
> > END),0),1),
> >
> > SUM(mtlburunitcost)/ISNULL(NULLIF(SUM(CASE WHEN mtlburunitcost > 0 THEN 1
> > ELSE 0 END),0),1),
> >
> > SUM(lbrunitcost)/ISNULL(NULLIF(SUM(CASE WHEN lbrunitcost > 0 THEN 1 ELSE 0
> > END),0),1)
> >
> > FROM Mfgsys803..PartTran (NOLOCK)
> >
> > WHERE Company ='KC' AND sysdate_<= '2013-08-16'
> >
> > AND PartNum in ('10000020', '10000106','1000ASTEN')
> >
> > AND CostMethod IN ('S')
> >
> > AND InventoryTrans =1
> >
> > Group By Company, PartNum
> >
> > Order By PartNum
> >
> > *Jose C Gomez*
> > *Software Engineer*
> > *
> > *
> > *
> > *T: 904.469.1524 mobile
> > E: jose@...<mailto:jose%40josecgomez.com>
> > http://www.josecgomez.com
> > <http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez
> > >
> > <http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
> > <http://www.josecgomez.com/professional-resume/>
> > <http://www.josecgomez.com/feed/>
> > <http://www.usdoingstuff.com>
> >
> > *Quis custodiet ipsos custodes?*
> >
> > [Non-text portions of this message have been removed]
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>