Solution for Multiple User Costs Applied to a Single Item

I have found an issue in Postbooks 3.8.1 (other versions as well, I believe) to do with assigning user costs to items. You can assign multiple user-defined costs to an item, but when you Post Production for a Work Order for that Item, only one user cost gets applied against your Work In Progress (WIP) cost. This means that when you close the work order, you get a Variance posted for the other user costs.

The Problem

The postProduction postGreSQL function in the xTuple Database retrieves user costs to apply against WIP using this piece of code:


-- ROB Increase this W/O's WIP value for custom costing
UPDATE wo
SET wo_wipvalue = (wo_wipvalue + (itemcost_stdcost * _parentQty))
FROM costelem, itemcost, costcat, itemsite, item
WHERE
((wo_id=pWoid) AND
(wo_itemsite_id=itemsite_id) AND
(itemsite_item_id=item_id) AND
(costelem_id = itemcost_costelem_id) AND
(itemcost_item_id = itemsite_item_id) AND
(itemsite_costcat_id = costcat_id) AND
(costelem_exp_accnt_id) IS NOT NULL AND
(costelem_sys = false));

This grabs a huge amount of redundant tuples for the column itemcost_stdcost, but only chooses the first of these tuples to add to the wo_wipvalue.
This leaves the other user costs still applied to WIP, to be posted as a Variance when the work order is closed. In effect, you cannot have multiple user defined standard costs, or you will have them credited in Item Costs and as a Variance. You will be double-billing yourself.

A Quick Solution
I changed the postProduction Function to find all distinct user costs associated with an Item, sum them, and apply them against Work In Progress when Posting Production.
To do this I created another variable in the function: _userCosts NUMERIC; -- new variable created for tracking all user costs

I then replaced the code listed above with this:
-- Grab All User Defined Costs associated with the work order
SELECT SUM(not_happy.itemcost_stdcost) INTO _userCosts
FROM (SELECT DISTINCT itemcost.itemcost_id, itemcost.itemcost_stdcost
FROM costelem, itemcost, costcat, itemsite, item, wo
WHERE ((wo.wo_id = pWoid) AND
(wo.wo_itemsite_id = itemsite.itemsite_id) AND
(costelem.costelem_id = itemcost.itemcost_costelem_id) AND
(itemcost.itemcost_item_id = itemsite.itemsite_item_id) AND
(itemsite.itemsite_costcat_id = costcat.costcat_id) AND
(costelem.costelem_exp_accnt_id) IS NOT NULL AND
(costelem.costelem_sys = FALSE))) AS not_happy;

-- ROB Increase this W/O's WIP value for custom costing
UPDATE wo
SET wo_wipvalue = (wo_wipvalue + (_userCosts * _parentQty)); --this was the problem here!!!
-- The old code returns a huge table, but only the first row is taken. Need to SUM the distinct costs, and then apply them against WIP.
I have tested this with multiple user costs, single user costs, and no user costs, and all cases work. This was a quick change for the coding, but it took me a lot of time and headache to figure out why we were getting HUGE manufacturing variances every month. Hopefully this saves you some time so you can put a better fix in for other users.

Note: This hasn't been changed in our production environment yet, we are taking some time to run through it in our sandbox to see if there is anything I missed when I changed the function. I'll update with any more info we gain from this.