If you are using Divisions in Control each part has the ability to store a different average cost for that specific division. There is a setting under Setup | Accounting Setup | General, under the section labeled Inventory named “Force part costs to be the same across Divisions”, if this is not enabled this you could see differing costs across your divisions which can lead to what looks like pricing irregularities.

If you do not want the part cost to be different across divisions, enable the option noted above and execute these SQLs. Executing these SQL's can have an effect on the pricing of your current estimates/orders. It can also cause GL inconsistencies for your average costing if you are recording the true GL value of you inventory from within Control. This does not apply to most users, but does to some. If you are unsure what effect this really has, do not run these queries.

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

-- This query will list any parts which have different part costs across the divisions.
-- You will need to manually code in your DIVISIONID's, you can obtain these from the EmployeeGroup Table.
-- SELECT * FROM EmployeeGroup WHERE IsDivision=1
SELECT * FROM (
	SELECT
	ItemName
	,UnitCost
	,(SELECT TOP 1 AverageCost FROM Inventory WITH(NOLOCK) WHERE PartID = P.ID AND DivisionID = 10 AND ClassTypeID = 12201) AS Division1_PartCost
	,(SELECT TOP 1 AverageCost FROM Inventory WITH(NOLOCK) WHERE PartID = P.ID AND DivisionID = 1000 AND ClassTypeID = 12201) AS Division2_PartCost
	,(SELECT TOP 1 AverageCost FROM Inventory WITH(NOLOCK) WHERE PartID = P.ID AND DivisionID = 1003 AND ClassTypeID = 12201) AS Division3_PartCost
	FROM Part P WITH(NOLOCK)
) T
WHERE
Division1_PartCost  Division2_PartCost 
	OR Division1_PartCost  Division3_PartCost 
	OR Division2_PartCost   Division3_PartCost 
-- This query updates all parts to use the part's Unit Cost.
-- You should first verify that users do not want different costs across different divisions before doing so,
-- in addition it would be best to give the end-user this list in the event that they need to adjust any part costs after this has been run.
-- DO NOT RUN THIS QUERY IF THEY ARE USING THE ACCOUNTING & INVENTORY MODULES TO HANDLE THEIR TRUE INVENTORY
-- FROM AN ACCOUNTING STANDPOINT, as the GL Entries will not support the change in UNIT COST you are forcing 
-- ONTO THE INVENTORY RECORDS.
UPDATE Inventory
SET AverageCost = COALESCE((SELECT TOP 1 UnitCost FROM Part WHERE ID = Inventory.PartID), AverageCost)
WHERE ClassTypeID = 12201
;
  • Entered : 2/20/2013
You could leave a comment if you were logged in.