Part Costs Last Update

Yahoo Archive
1
The costing workbench is the methodology by which you update cost in
your system.



The only record that has a date is going to be in the PartTran table for
each bin that exist when cost changes are posted. There are no entry
date/mod dates kept in any of the part tables that I have ever been able
to find.



The costing workbench has a post action that launches a systematic
adjustment of inventory out from each bin at the old cost and then
entering back in to the same bin at the new cost. ( It is also the
point where lot sizes are adjusted as well). You are required to
provide a reason code when performing the post. I would advise you to
create reason code used only for cost adjustments. You could then look
for that -or- it may be good enough to just look for PartTran.TranType =
'ADJ-CST' and find the latest TranDate.



John A. Hatcher

Manager of IS

Versa Products Co., Inc.

(201) 518-5948

(201) 843-2400 x4148

(201) 843-2931 (fax)





[Non-text portions of this message have been removed]
2
I'm trying to generate a report to show parts that haven't had a cost update in X days.

It appears the only way I can do this is through the PartTrans table. I have Part table linked to PartTrans with a last condition. I want to make sure I'm getting only cost updates and not all the other transactions that can occur. I believe I can get this by grabbing any PartTrans of trans type PUR-MTL, PUR-STK or ADJ-CST. Am I missing any?

Alternatively, am I missing some easier way of accomplishing this? PartCost table doesn't have a last transaction date unfortunately.

Thanks,
Ken




[Non-text portions of this message have been removed]
3
Ken,

I created a customization in VB for exactly this purpose, but by single part only. This is tied to a button in the part tracker form and displays the result in a text box. This works for the part currently loaded in the part tracker. Please note that my code isint exactly the most efficient code in the world as I am in the process of optimizing this. (You could probably put some aggregation function in the SQL statement itself).

If you were to want a list of parts, then you could probably write a SQL statement (assuming your DB is SQL) which gets the MAX(tran date) and does a GROUP BY(part number) on the costpart DB. You can probably combine that with the parttran DB if you needed transaction data like ADJ, etc.... this could be done very easily in MS Access.... (oops, did i just say that out loud!?!)

Private Sub btnASHShowDates_Click(ByVal Sender As Object, ByVal Args As System.EventArgs) Handles btnASHShowDates.Click

Dim partNum = edvPartCost.dataView(edvPartCost.row)("partnum")
If (getDates(partNum) = False) Then
lblASHEffDate.text = "UNKNOWN"
End If
End Sub

Private Function getDates(ByVal partnum as String) as Boolean
Dim ret as Boolean
Dim cnxString as String = "...." ' replace with your connection string
Dim cnxSQL as String = "SELECT groupid, partnum, effectivedate" & _
" FROM costpart " & _
" WHERE partnum = '" & partnum & "'"
Dim cnxCN as SqlConnection
Dim cnxCMD as New SqlCommand
Dim cnxDR as SqlDataReader

Dim str as String = ""
Dim dates() as String
Dim d1 as Date

ret = true

try
cnxCN = New SqlConnection(cnxString)
With cnxCMD
.CommandText = cnxSQL
.Connection = cnxCN
End With
cnxCN.Open()
cnxDR = cnxCMD.ExecuteReader(CommandBehavior.CloseConnection)
While cnxDR.Read
str += " " & cnxDR("effectivedate")
End While

dates = Split(str)
dim i as integer
' eff dates first
d1 = CDate("January 1, 1900") ' arbitary date used for comparision
for i = 1 to dates.length - 1
if CDate(dates(i)) > d1 then
d1 = CDate(dates(i))
End if
next

If d1 = CDate("January 1, 1900") Then
lblASHEffDate.text = "NOT SET"
Else
dates = split(d1.tostring())
lblASHEffDate.text = dates(0)
End If

catch ex as exception
MessageBox.Show("Please report this message to an Administrator. Error in getdates: " & ex.message & vbCrLf & vbCrLf & ex.StackTrace)
ret = false
end try

return ret
End Function


Thanks,
Kunal




________________________________
From: Ken Williams <kwilliams@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Sent: Wed, October 28, 2009 11:51:23 AM
Subject: [Vantage] Part Costs Last Update


I'm trying to generate a report to show parts that haven't had a cost update in X days.

It appears the only way I can do this is through the PartTrans table. I have Part table linked to PartTrans with a last condition. I want to make sure I'm getting only cost updates and not all the other transactions that can occur. I believe I can get this by grabbing any PartTrans of trans type PUR-MTL, PUR-STK or ADJ-CST. Am I missing any?

Alternatively, am I missing some easier way of accomplishing this? PartCost table doesn't have a last transaction date unfortunately.

Thanks,
Ken

[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]
4
The CostPart table in our system is empty. Is there something I'm missing? I looked at that table earlier and assumed it was a temp table of some sort, but it appears your SQL query is using it ("FROM costpart").

Ken

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Kunal Ganguly
Sent: Wednesday, October 28, 2009 12:13 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Part Costs Last Update



Ken,

I created a customization in VB for exactly this purpose, but by single part only. This is tied to a button in the part tracker form and displays the result in a text box. This works for the part currently loaded in the part tracker. Please note that my code isint exactly the most efficient code in the world as I am in the process of optimizing this. (You could probably put some aggregation function in the SQL statement itself).

If you were to want a list of parts, then you could probably write a SQL statement (assuming your DB is SQL) which gets the MAX(tran date) and does a GROUP BY(part number) on the costpart DB. You can probably combine that with the parttran DB if you needed transaction data like ADJ, etc.... this could be done very easily in MS Access.... (oops, did i just say that out loud!?!)

Private Sub btnASHShowDates_Click(ByVal Sender As Object, ByVal Args As System.EventArgs) Handles btnASHShowDates.Click

Dim partNum = edvPartCost.dataView(edvPartCost.row)("partnum")
If (getDates(partNum) = False) Then
lblASHEffDate.text = "UNKNOWN"
End If
End Sub

Private Function getDates(ByVal partnum as String) as Boolean
Dim ret as Boolean
Dim cnxString as String = "...." ' replace with your connection string
Dim cnxSQL as String = "SELECT groupid, partnum, effectivedate" & _
" FROM costpart " & _
" WHERE partnum = '" & partnum & "'"
Dim cnxCN as SqlConnection
Dim cnxCMD as New SqlCommand
Dim cnxDR as SqlDataReader

Dim str as String = ""
Dim dates() as String
Dim d1 as Date

ret = true

try
cnxCN = New SqlConnection(cnxString)
With cnxCMD
.CommandText = cnxSQL
.Connection = cnxCN
End With
cnxCN.Open()
cnxDR = cnxCMD.ExecuteReader(CommandBehavior.CloseConnection)
While cnxDR.Read
str += " " & cnxDR("effectivedate")
End While

dates = Split(str)
dim i as integer
' eff dates first
d1 = CDate("January 1, 1900") ' arbitary date used for comparision
for i = 1 to dates.length - 1
if CDate(dates(i)) > d1 then
d1 = CDate(dates(i))
End if
next

If d1 = CDate("January 1, 1900") Then
lblASHEffDate.text = "NOT SET"
Else
dates = split(d1.tostring())
lblASHEffDate.text = dates(0)
End If

catch ex as exception
MessageBox.Show("Please report this message to an Administrator. Error in getdates: " & ex.message & vbCrLf & vbCrLf & ex.StackTrace)
ret = false
end try

return ret
End Function

Thanks,
Kunal

________________________________
From: Ken Williams <kwilliams@...<mailto:kwilliams%40intermountainelectronics.com>>
To: "vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>" <vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>>
Sent: Wed, October 28, 2009 11:51:23 AM
Subject: [Vantage] Part Costs Last Update

I'm trying to generate a report to show parts that haven't had a cost update in X days.

It appears the only way I can do this is through the PartTrans table. I have Part table linked to PartTrans with a last condition. I want to make sure I'm getting only cost updates and not all the other transactions that can occur. I believe I can get this by grabbing any PartTrans of trans type PUR-MTL, PUR-STK or ADJ-CST. Am I missing any?

Alternatively, am I missing some easier way of accomplishing this? PartCost table doesn't have a last transaction date unfortunately.

Thanks,
Ken

[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]
5
Are you only interested in purchased items? What about MFG-STK or MFG-MTL.

Michelle de la Vega
Business Applications Manager
Cold Jet, LLC
455 Wards Corner Road
Loveland, Ohio 45140
USA
+1 513-716-6400 (office)
+1 513-382-3281 (mobile)
+1 513-831-1209 (fax)
www.coldjet.com<http://www.coldjet.com>
[cid:image001.jpg@01CA57E3.C6085CF0]

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Ken Williams
Sent: Wednesday, October 28, 2009 12:51 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Part Costs Last Update



I'm trying to generate a report to show parts that haven't had a cost update in X days.

It appears the only way I can do this is through the PartTrans table. I have Part table linked to PartTrans with a last condition. I want to make sure I'm getting only cost updates and not all the other transactions that can occur. I believe I can get this by grabbing any PartTrans of trans type PUR-MTL, PUR-STK or ADJ-CST. Am I missing any?

Alternatively, am I missing some easier way of accomplishing this? PartCost table doesn't have a last transaction date unfortunately.

Thanks,
Ken

[Non-text portions of this message have been removed]



[Non-text portions of this message have been removed]
6
Currently only interested in purchased items. We have limited MFG-STK/MFG-MTL items that I'm concerned about last updated costs on.


From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Michelle de la Vega
Sent: Wednesday, October 28, 2009 1:32 PM
To: vantage@yahoogroups.com
Subject: [Vantage] RE: Part Costs Last Update



Are you only interested in purchased items? What about MFG-STK or MFG-MTL.

Michelle de la Vega
Business Applications Manager
Cold Jet, LLC
455 Wards Corner Road
Loveland, Ohio 45140
USA
+1 513-716-6400 (office)
+1 513-382-3281 (mobile)
+1 513-831-1209 (fax)
www.coldjet.com<http://www.coldjet.com>
[cid:image001.jpg@01CA57E3.C6085CF0<mailto:image001.jpg%4001CA57E3.C6085CF0>]

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Ken Williams
Sent: Wednesday, October 28, 2009 12:51 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Part Costs Last Update

I'm trying to generate a report to show parts that haven't had a cost update in X days.

It appears the only way I can do this is through the PartTrans table. I have Part table linked to PartTrans with a last condition. I want to make sure I'm getting only cost updates and not all the other transactions that can occur. I believe I can get this by grabbing any PartTrans of trans type PUR-MTL, PUR-STK or ADJ-CST. Am I missing any?

Alternatively, am I missing some easier way of accomplishing this? PartCost table doesn't have a last transaction date unfortunately.

Thanks,
Ken

[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]
7
from data dictionary about costpart -
"This table contains a copy of all of the part records available at the time a cost group is created. Each record contains the CostGrp.GroupID as a unique identifier for the cost group to which the cost part set belongs. "

Not sure where I got this info (either this group or epicor support), but this table gets populated when a part is rolled up (Production mgmt | Engineering | Costing workbench). If your table is empty, then I would venture a guess that no part has ever been rolled up in your database using vantage. Did you guys migrate from something else recently?

Thanks,
Kunal




________________________________
From: Ken Williams <kwilliams@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Sent: Wed, October 28, 2009 2:20:45 PM
Subject: RE: [Vantage] Part Costs Last Update


The CostPart table in our system is empty. Is there something I'm missing? I looked at that table earlier and assumed it was a temp table of some sort, but it appears your SQL query is using it ("FROM costpart").

Ken

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf Of Kunal Ganguly
Sent: Wednesday, October 28, 2009 12:13 PM
To: vantage@yahoogroups .com
Subject: Re: [Vantage] Part Costs Last Update

Ken,

I created a customization in VB for exactly this purpose, but by single part only. This is tied to a button in the part tracker form and displays the result in a text box. This works for the part currently loaded in the part tracker. Please note that my code isint exactly the most efficient code in the world as I am in the process of optimizing this. (You could probably put some aggregation function in the SQL statement itself).

If you were to want a list of parts, then you could probably write a SQL statement (assuming your DB is SQL) which gets the MAX(tran date) and does a GROUP BY(part number) on the costpart DB. You can probably combine that with the parttran DB if you needed transaction data like ADJ, etc.... this could be done very easily in MS Access.... (oops, did i just say that out loud!?!)

Private Sub btnASHShowDates_ Click(ByVal Sender As Object, ByVal Args As System.EventArgs) Handles btnASHShowDates. Click

Dim partNum = edvPartCost. dataView( edvPartCost. row)("partnum" )
If (getDates(partNum) = False) Then
lblASHEffDate. text = "UNKNOWN"
End If
End Sub

Private Function getDates(ByVal partnum as String) as Boolean
Dim ret as Boolean
Dim cnxString as String = "...." ' replace with your connection string
Dim cnxSQL as String = "SELECT groupid, partnum, effectivedate" & _
" FROM costpart " & _
" WHERE partnum = '" & partnum & "'"
Dim cnxCN as SqlConnection
Dim cnxCMD as New SqlCommand
Dim cnxDR as SqlDataReader

Dim str as String = ""
Dim dates() as String
Dim d1 as Date

ret = true

try
cnxCN = New SqlConnection( cnxString)
With cnxCMD
.CommandText = cnxSQL
.Connection = cnxCN
End With
cnxCN.Open()
cnxDR = cnxCMD.ExecuteReade r(CommandBehavio r.CloseConnectio n)
While cnxDR.Read
str += " " & cnxDR("effectivedat e")
End While

dates = Split(str)
dim i as integer
' eff dates first
d1 = CDate("January 1, 1900") ' arbitary date used for comparision
for i = 1 to dates.length - 1
if CDate(dates( i)) > d1 then
d1 = CDate(dates( i))
End if
next

If d1 = CDate("January 1, 1900") Then
lblASHEffDate. text = "NOT SET"
Else
dates = split(d1.tostring( ))
lblASHEffDate. text = dates(0)
End If

catch ex as exception
MessageBox.Show( "Please report this message to an Administrator. Error in getdates: " & ex.message & vbCrLf & vbCrLf & ex.StackTrace)
ret = false
end try

return ret
End Function

Thanks,
Kunal

____________ _________ _________ __
From: Ken Williams <kwilliams@intermoun tainelectronics. com<mailto:kwilliams% 40intermountaine lectronics. com>>
To: "vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com>" <vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com>>
Sent: Wed, October 28, 2009 11:51:23 AM
Subject: [Vantage] Part Costs Last Update

I'm trying to generate a report to show parts that haven't had a cost update in X days.

It appears the only way I can do this is through the PartTrans table. I have Part table linked to PartTrans with a last condition. I want to make sure I'm getting only cost updates and not all the other transactions that can occur. I believe I can get this by grabbing any PartTrans of trans type PUR-MTL, PUR-STK or ADJ-CST. Am I missing any?

Alternatively, am I missing some easier way of accomplishing this? PartCost table doesn't have a last transaction date unfortunately.

Thanks,
Ken

[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]







[Non-text portions of this message have been removed]
8
We've never used the costing workbench...we've been on Vantage for nearly 3 years now.

We have the great fortune of being a build-to-order shop, so the engineering workbench & costing workbench haven't been of much interest to us. Perhaps I'm missing something on the costing workbench? It looks to me that that is used to determine standard costs for parts, generally ones you manufacture. Beings ours are unique each time and we use last costing, I don't see how this tool can help us.

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Kunal Ganguly
Sent: Wednesday, October 28, 2009 1:39 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Part Costs Last Update



from data dictionary about costpart -
"This table contains a copy of all of the part records available at the time a cost group is created. Each record contains the CostGrp.GroupID as a unique identifier for the cost group to which the cost part set belongs. "

Not sure where I got this info (either this group or epicor support), but this table gets populated when a part is rolled up (Production mgmt | Engineering | Costing workbench). If your table is empty, then I would venture a guess that no part has ever been rolled up in your database using vantage. Did you guys migrate from something else recently?

Thanks,
Kunal

________________________________
From: Ken Williams <kwilliams@...<mailto:kwilliams%40intermountainelectronics.com>>
To: "vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>" <vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>>
Sent: Wed, October 28, 2009 2:20:45 PM
Subject: RE: [Vantage] Part Costs Last Update

The CostPart table in our system is empty. Is there something I'm missing? I looked at that table earlier and assumed it was a temp table of some sort, but it appears your SQL query is using it ("FROM costpart").

Ken

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf Of Kunal Ganguly
Sent: Wednesday, October 28, 2009 12:13 PM
To: vantage@yahoogroups .com
Subject: Re: [Vantage] Part Costs Last Update

Ken,

I created a customization in VB for exactly this purpose, but by single part only. This is tied to a button in the part tracker form and displays the result in a text box. This works for the part currently loaded in the part tracker. Please note that my code isint exactly the most efficient code in the world as I am in the process of optimizing this. (You could probably put some aggregation function in the SQL statement itself).

If you were to want a list of parts, then you could probably write a SQL statement (assuming your DB is SQL) which gets the MAX(tran date) and does a GROUP BY(part number) on the costpart DB. You can probably combine that with the parttran DB if you needed transaction data like ADJ, etc.... this could be done very easily in MS Access.... (oops, did i just say that out loud!?!)

Private Sub btnASHShowDates_ Click(ByVal Sender As Object, ByVal Args As System.EventArgs) Handles btnASHShowDates. Click

Dim partNum = edvPartCost. dataView( edvPartCost. row)("partnum" )
If (getDates(partNum) = False) Then
lblASHEffDate. text = "UNKNOWN"
End If
End Sub

Private Function getDates(ByVal partnum as String) as Boolean
Dim ret as Boolean
Dim cnxString as String = "...." ' replace with your connection string
Dim cnxSQL as String = "SELECT groupid, partnum, effectivedate" & _
" FROM costpart " & _
" WHERE partnum = '" & partnum & "'"
Dim cnxCN as SqlConnection
Dim cnxCMD as New SqlCommand
Dim cnxDR as SqlDataReader

Dim str as String = ""
Dim dates() as String
Dim d1 as Date

ret = true

try
cnxCN = New SqlConnection( cnxString)
With cnxCMD
.CommandText = cnxSQL
.Connection = cnxCN
End With
cnxCN.Open()
cnxDR = cnxCMD.ExecuteReade r(CommandBehavio r.CloseConnectio n)
While cnxDR.Read
str += " " & cnxDR("effectivedat e")
End While

dates = Split(str)
dim i as integer
' eff dates first
d1 = CDate("January 1, 1900") ' arbitary date used for comparision
for i = 1 to dates.length - 1
if CDate(dates( i)) > d1 then
d1 = CDate(dates( i))
End if
next

If d1 = CDate("January 1, 1900") Then
lblASHEffDate. text = "NOT SET"
Else
dates = split(d1.tostring( ))
lblASHEffDate. text = dates(0)
End If

catch ex as exception
MessageBox.Show( "Please report this message to an Administrator. Error in getdates: " & ex.message & vbCrLf & vbCrLf & ex.StackTrace)
ret = false
end try

return ret
End Function

Thanks,
Kunal

____________ _________ _________ __
From: Ken Williams <kwilliams@intermoun tainelectronics. com<mailto:kwilliams% 40intermountaine lectronics. com>>
To: "vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com>" <vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com>>
Sent: Wed, October 28, 2009 11:51:23 AM
Subject: [Vantage] Part Costs Last Update

I'm trying to generate a report to show parts that haven't had a cost update in X days.

It appears the only way I can do this is through the PartTrans table. I have Part table linked to PartTrans with a last condition. I want to make sure I'm getting only cost updates and not all the other transactions that can occur. I believe I can get this by grabbing any PartTrans of trans type PUR-MTL, PUR-STK or ADJ-CST. Am I missing any?

Alternatively, am I missing some easier way of accomplishing this? PartCost table doesn't have a last transaction date unfortunately.

Thanks,
Ken

[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]

[Non-text portions of this message have been removed]



[Non-text portions of this message have been removed]
9
CostPart is used by costing workbench for rolling and posting cost
adjustments.



PartCost maintains one record per part number at all times. This is
probably the one you are looking for. This shows the current std, avg
and last cost data for each part.



John A. Hatcher

Manager of IS

Versa Products Co., Inc.

(201) 518-5948

(201) 843-2400 x4148

(201) 843-2931 (fax)





[Non-text portions of this message have been removed]
10
PartCost has no last transaction date.

What I am ultimately looking for is a list of parts that haven't had an updated cost in X days.

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Hatcher, John
Sent: Wednesday, October 28, 2009 2:29 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Part Costs Last Update



CostPart is used by costing workbench for rolling and posting cost
adjustments.

PartCost maintains one record per part number at all times. This is
probably the one you are looking for. This shows the current std, avg
and last cost data for each part.

John A. Hatcher

Manager of IS

Versa Products Co., Inc.

(201) 518-5948

(201) 843-2400 x4148

(201) 843-2931 (fax)

[Non-text portions of this message have been removed]



[Non-text portions of this message have been removed]
11
In that case, I am not sure how you will be able to get the cost roll up date, unless there is another table/procedure that rolls up costs.

Thanks,
Kunal




________________________________
From: Ken Williams <kwilliams@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Sent: Wed, October 28, 2009 2:47:55 PM
Subject: RE: [Vantage] Part Costs Last Update


We've never used the costing workbench... we've been on Vantage for nearly 3 years now.

We have the great fortune of being a build-to-order shop, so the engineering workbench & costing workbench haven't been of much interest to us. Perhaps I'm missing something on the costing workbench? It looks to me that that is used to determine standard costs for parts, generally ones you manufacture. Beings ours are unique each time and we use last costing, I don't see how this tool can help us.

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf Of Kunal Ganguly
Sent: Wednesday, October 28, 2009 1:39 PM
To: vantage@yahoogroups .com
Subject: Re: [Vantage] Part Costs Last Update

from data dictionary about costpart -
"This table contains a copy of all of the part records available at the time a cost group is created. Each record contains the CostGrp.GroupID as a unique identifier for the cost group to which the cost part set belongs. "

Not sure where I got this info (either this group or epicor support), but this table gets populated when a part is rolled up (Production mgmt | Engineering | Costing workbench). If your table is empty, then I would venture a guess that no part has ever been rolled up in your database using vantage. Did you guys migrate from something else recently?

Thanks,
Kunal

____________ _________ _________ __
From: Ken Williams <kwilliams@intermoun tainelectronics. com<mailto:kwilliams% 40intermountaine lectronics. com>>
To: "vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com>" <vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com>>
Sent: Wed, October 28, 2009 2:20:45 PM
Subject: RE: [Vantage] Part Costs Last Update

The CostPart table in our system is empty. Is there something I'm missing? I looked at that table earlier and assumed it was a temp table of some sort, but it appears your SQL query is using it ("FROM costpart").

Ken

From: vantage@yahoogroups .com [mailto:vantage@ yahoogroups .com] On Behalf Of Kunal Ganguly
Sent: Wednesday, October 28, 2009 12:13 PM
To: vantage@yahoogroups .com
Subject: Re: [Vantage] Part Costs Last Update

Ken,

I created a customization in VB for exactly this purpose, but by single part only. This is tied to a button in the part tracker form and displays the result in a text box. This works for the part currently loaded in the part tracker. Please note that my code isint exactly the most efficient code in the world as I am in the process of optimizing this. (You could probably put some aggregation function in the SQL statement itself).

If you were to want a list of parts, then you could probably write a SQL statement (assuming your DB is SQL) which gets the MAX(tran date) and does a GROUP BY(part number) on the costpart DB. You can probably combine that with the parttran DB if you needed transaction data like ADJ, etc.... this could be done very easily in MS Access.... (oops, did i just say that out loud!?!)

Private Sub btnASHShowDates_ Click(ByVal Sender As Object, ByVal Args As System.EventArgs) Handles btnASHShowDates. Click

Dim partNum = edvPartCost. dataView( edvPartCost. row)("partnum" )
If (getDates(partNum) = False) Then
lblASHEffDate. text = "UNKNOWN"
End If
End Sub

Private Function getDates(ByVal partnum as String) as Boolean
Dim ret as Boolean
Dim cnxString as String = "...." ' replace with your connection string
Dim cnxSQL as String = "SELECT groupid, partnum, effectivedate" & _
" FROM costpart " & _
" WHERE partnum = '" & partnum & "'"
Dim cnxCN as SqlConnection
Dim cnxCMD as New SqlCommand
Dim cnxDR as SqlDataReader

Dim str as String = ""
Dim dates() as String
Dim d1 as Date

ret = true

try
cnxCN = New SqlConnection( cnxString)
With cnxCMD
.CommandText = cnxSQL
.Connection = cnxCN
End With
cnxCN.Open()
cnxDR = cnxCMD.ExecuteReade r(CommandBehavio r.CloseConnectio n)
While cnxDR.Read
str += " " & cnxDR("effectivedat e")
End While

dates = Split(str)
dim i as integer
' eff dates first
d1 = CDate("January 1, 1900") ' arbitary date used for comparision
for i = 1 to dates.length - 1
if CDate(dates( i)) > d1 then
d1 = CDate(dates( i))
End if
next

If d1 = CDate("January 1, 1900") Then
lblASHEffDate. text = "NOT SET"
Else
dates = split(d1.tostring( ))
lblASHEffDate. text = dates(0)
End If

catch ex as exception
MessageBox.Show( "Please report this message to an Administrator. Error in getdates: " & ex.message & vbCrLf & vbCrLf & ex.StackTrace)
ret = false
end try

return ret
End Function

Thanks,
Kunal

____________ _________ _________ __
From: Ken Williams <kwilliams@intermou n tainelectronics. com<mailto:kwilliam s% 40intermountaine lectronics. com>>
To: "vantage@yahoogroup s .com<mailto: vantage% 40yahoogroups. com>" <vantage@yahoogroup s .com<mailto: vantage% 40yahoogroups. com>>
Sent: Wed, October 28, 2009 11:51:23 AM
Subject: [Vantage] Part Costs Last Update

I'm trying to generate a report to show parts that haven't had a cost update in X days.

It appears the only way I can do this is through the PartTrans table. I have Part table linked to PartTrans with a last condition. I want to make sure I'm getting only cost updates and not all the other transactions that can occur. I believe I can get this by grabbing any PartTrans of trans type PUR-MTL, PUR-STK or ADJ-CST. Am I missing any?

Alternatively, am I missing some easier way of accomplishing this? PartCost table doesn't have a last transaction date unfortunately.

Thanks,
Ken

[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]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]




__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]