ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Evo-ERP and DBA Classic >> Manufacturing >> Last/Avg Cost
http://www.istechforum.com/YaBB.pl?num=1255014203

Message started by Juan on 10/08/09 at 08:03:23

Title: Last/Avg Cost
Post by Juan on 10/08/09 at 08:03:23

What will be the easiest and less painful way to change the last and average cost on a group of item? These items are all from one vendor
Thx
Juan

Title: Re: Last/Avg Cost
Post by GasGiant on 10/08/09 at 08:50:23

Backing out the troublesome transaction and reprocessing it is the "best" way. Knowing a little more about the situation would help.

Title: Re: Last/Avg Cost
Post by Juan on 10/09/09 at 06:47:08

Here is the whole scoop. We’re two companies, one is all marketing and the other is manufacturing. Marketing pays for the high price items, manufacturing for labor and low cost items. At one point we had PO’s at zero dollars from marketing, and this was fine not problems, but the owners decided to cost the BOMs so we needed it to price the PO’s from marketing. Since these PO’s do not get pay the RNI is overstated. Now they decided to go back to no cost on marketing’s PO’s, and to take all the cost from the items that marketing buys out of BOMs. The amount of items that we need to take the cost out is over one thousand
Thx
Juan

Title: Re: Last/Avg Cost
Post by GasGiant on 10/09/09 at 08:18:04

well, if all of the costs are over a certain amount, you could use a SQL query to zero out last and avg. you could also filter on some other code, like class if that is unique for the items in question. people here could help you with the query, which would be run in the Pervasive Control Center. another option would be to do it with Maintain Database, which would be a little more tedious, but more direct, as well.

Here is an example of a query that would grab all items with an average cost over $10

SELECT bkic_prod_code, bkic_prod_avgc FROM BKICMSTR WHERE bkic_prod_avgc > 10.00 AND bkic_prod_type = 'R'

this would give you a list and a count of purchased parts that cost over ten bucks. you may have a different number for the dollar figure and you may need to consider type N items as well as type R

once you tweak it to the point that your list is what you want, an update query can be written to do the dirty work.

Title: Re: Last/Avg Cost
Post by Juan on 10/09/09 at 08:25:15

All the items come from the same vendor (The marketing group)

Title: Re: Last/Avg Cost
Post by Lynn_Pantic on 10/09/09 at 09:55:51

That won't help much because primary vendor is stored in MTICMSTR and the Last and Average Cost are stored in BKICMSTR. Do they have anything else in common? Class and Category are stored in BKICMSTR.

Title: Re: Last/Avg Cost
Post by Juan on 10/09/09 at 10:20:27

No, that's the only thing in common
Thx
Juan

Title: Re: Last/Avg Cost
Post by GasGiant on 10/09/09 at 10:38:13

That would call for a subquery with a WHERE EXISTS inside the update query, which is not something I see a lot of, but it can be done. Is the vendor code field set for all of these items? That would be the limiting factor.

Title: Re: Last/Avg Cost
Post by Juan on 10/09/09 at 10:49:21

Yes, it is set with the same vendor code
Thx
Juan

Title: Re: Last/Avg Cost
Post by GasGiant on 10/10/09 at 07:31:12

Is there someone at your facility who knows how to use the Pervasive tools such as the Control Center? Have you used external reporting programs, such as Crystal Reports or Excel in the past? I'm trying to determine if you have ODBC DDFs in place and if your database exists in the control center, which are prerequisites to using the control center for running some queries.

It would also help to know what version of Pervasive you are running.