Cost for a specific date (historic)

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]
>
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@...
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]
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@01CE9A9D.3E1A7F70]<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]
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@01CE9A9D.3E1A7F70]<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]
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]
>