xTuple.com xTupleU Blog & News Customer Support

Price Schedules with Job Costed Items

I am trying to use nominal items on a price schedule for a customer. All of there items are job costed.

I am selling the items in UOM ‘EA’.
I have a paint on the bom which is bought and consumed in ‘GAL’

When I have the price schedule assigned to the customer. I get an error when trying to explode the work order.

It is attempting to convert the paint to ‘EA’ Which is not possible as the paint is only in GAL. Nor could it be possible because some parts require .1 gal and others require 1 gallon of paint.

It seems like a bug to me. I could force the function to return a number and not an error. But I don’t even know what the number is used for

I am fairly positive nothing is configured wrong

ERROR:  A conversion for item PAINTY from uom EA to uom GAL was not found.
CONTEXT:  PL/pgSQL function itemuomtouomratio(integer,integer,integer) line 145 at RAISE
SQL statement "SELECT   CASE WHEN (ipsitem_type = 'N') THEN
                 (ipsitem_price / itemuomtouomratio(_item.item_id, ipsitem_price_uom_id, _item.item_price_uom_id))
                WHEN (ipsitem_type = 'D') THEN
                 noNeg(currToLocal(ipshead_curr_id, COALESCE(pListPrice, _item.item_listprice), pAsOf) -
                      (currToLocal(ipshead_curr_id, COALESCE(pListPrice, _item.item_listprice), pAsOf) * ipsitem_discntprcnt) - ipsitem_fixedamtdiscount)
                WHEN ((ipsitem_type = 'M') AND _long30markups AND _wholesalepricecosting) THEN
                 (currToLocal(ipshead_curr_id, _item.item_listcost, pAsOf) / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                WHEN ((ipsitem_type = 'M') AND _long30markups) THEN
                 (currToLocal(ipshead_curr_id, _item.invcost, pAsOf) / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                WHEN (ipsitem_type = 'M' AND _wholesalepricecosting) THEN
                 (currToLocal(ipshead_curr_id, _item.item_listcost, pAsOf) + (currToLocal(ipshead_curr_id, _item.item_listcost, pAsOf) * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                WHEN (ipsitem_type = 'M') THEN
                 (currToLocal(ipshead_curr_id, _item.invcost, pAsOf) + (currToLocal(ipshead_curr_id, _item.invcost, pAsOf) * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount)
                ELSE 0.00
           END                FROM ipsiteminfo JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
  WHERE (ipsitem_id=pIpsitemid)"
PL/pgSQL function calcipsitemprice(integer,integer,integer,numeric,date) line 28 at SQL statement
SQL statement "SELECT               *, currToCurr(ipshead_curr_id,
                  (protoprice / itemuomtouomratio(pItemid, _item.item_price_uom_id, pPriceUOM)),
                  pEffective) AS rightprice
  FROM (
    SELECT *,
           CASE WHEN (COALESCE(ipsass_shipto_id, -1) > 0) THEN 1
                WHEN (COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0 AND COALESCE(ipsass_cust_id, -1) > 0) THEN 2
                WHEN (COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) THEN 3
                WHEN (COALESCE(ipsass_cust_id, -1) > 0) THEN 4
                WHEN (COALESCE(ipsass_custtype_id, -1) > 0) THEN 5
                WHEN (COALESCE(LENGTH(ipsass_custtype_pattern), 0) > 0) THEN 6
                WHEN (COALESCE(ipsass_shipzone_id, -1) > 0) THEN 7
                WHEN (COALESCE(ipsass_saletype_id, -1) > 0) THEN 8
                ELSE 99
           END AS assignseq,
                            pEffective) AS protoprice,
           CASE WHEN (ipsitem_item_id=_item.item_id) THEN itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, pQtyuom, ipsitem_qtybreak)
                ELSE ipsitem_qtybreak
           END AS protoqtybreak,
           (COALESCE(ipsitem_price_uom_id, -1)=COALESCE(pPriceUOM,-1)) AS uommatched,
           CASE WHEN (_itempricingprecedence) THEN (COALESCE(ipsitem_item_id, -1)=_item.item_id)
                ELSE true END AS itemmatched
    FROM ipsass JOIN ipshead ON (ipshead_id=ipsass_ipshead_id AND NOT ipshead_listprice)
                JOIN ipsiteminfo ON (ipsitem_ipshead_id=ipshead_id)
    WHERE ( (ipsitem_item_id=_item.item_id) OR (ipsitem_prodcat_id=_item.item_prodcat_id) )
      AND (_asof BETWEEN ipshead_effective AND (ipshead_expires - 1))
      AND ( (ipsitem_warehous_id=pSiteid) OR (ipsitem_warehous_id IS NULL) )
      AND ( (ipsass_shipto_id=_shipto.shipto_id)
       OR   ((COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) AND (ipsass_cust_id > -1) AND (COALESCE(_shipto.shipto_num, '') ~ ipsass_shipto_pattern) AND (ipsass_cust_id = _cust.cust_id))
       OR   ((COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) AND (ipsass_cust_id = -1) AND (COALESCE(_shipto.shipto_num, '') ~ ipsass_shipto_pattern))
       OR   ((COALESCE(LENGTH(ipsass_shipto_pattern), 0) = 0) AND (ipsass_cust_id=_cust.cust_id))
       OR   (ipsass_custtype_id=_cust.cust_custtype_id)
       OR   ((COALESCE(LENGTH(ipsass_custtype_pattern), 0) > 0) AND (COALESCE(_cust.custtype_code, '') ~ ipsass_custtype_pattern))
       OR   ((COALESCE(ipsass_shipzone_id, 0) > 0) AND (ipsass_shipzone_id=pShipZoneid))
       OR   ((COALESCE(ipsass_saletype_id, 0 ) > 0) AND (ipsass_saletype_id=pSaleTypeid)) )
  ) AS proto
  WHERE (protoqtybreak <= pQty)
  ORDER BY assignseq, itemmatched DESC, protoqtybreak DESC, rightprice
  LIMIT 1"
PL/pgSQL function itemipsprice(integer,integer,integer,numeric,integer,integer,integer,date,date,integer,integer,integer) line 93 at SQL statement
SQL statement "INSERT INTO womatl
  ( womatl_wo_id, womatl_bomitem_id, womatl_wooper_id, womatl_schedatwooper,
    womatl_itemsite_id, womatl_duedate,
    womatl_uom_id, womatl_qtyfxd, womatl_qtyper, womatl_scrap,
    womatl_qtyiss, womatl_qtywipscrap,
    womatl_lastissue, womatl_lastreturn, womatl_cost,
    womatl_picklist, womatl_createwo, womatl_issuewo,
    womatl_issuemethod, womatl_notes, womatl_ref,
    womatl_price )
  SELECT wo_id, bomitem_id, bomitem_booitem_seq_id, bomitem_schedatwooper,
         matl_itemsite, duedate,
         bomitem_uom_id, bomitem_qtyfxd, bomitem_qtyper, bomitem_scrap,
         qtyreq, 0, 0,
         startOfTime(), startOfTime(), 0,
         item_picklist, ( (item_type='M') AND (bomitem_createwo) ), issuewo,
         bomitem_issuemethod, bomitem_notes, bomitem_ref,
         CASE WHEN (price=-9999.0) THEN 0.0
              ELSE price
  FROM (SELECT wo_id, bomitem_id, bomitem_booitem_seq_id, bomitem_schedatwooper,
               bomitem_uom_id, bomitem_qtyfxd, bomitem_qtyper, bomitem_scrap,
               bomitem_createwo, bomitem_issuemethod, bomitem_notes, bomitem_ref,
               item_picklist, item_type,
               cs.itemsite_id AS matl_itemsite,
               CASE WHEN bomitem_schedatwooper THEN COALESCE(calcWooperStartStub(wo_id,bomitem_booitem_seq_id), wo_startdate)
                    ELSE wo_startdate
               END AS duedate,
               roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id), (bomitem_qtyfxd + bomitem_qtyper * wo_qtyord) * (1 + bomitem_scrap)) AS qtyreq,
               CASE WHEN ( (item_type='M') AND (bomitem_issuewo) ) THEN TRUE
                    WHEN (cs.itemsite_costmethod='J') THEN TRUE
                    ELSE FALSE
               END AS issuewo,
               CASE WHEN (cohead_id IS NULL) THEN item_listprice
                    ELSE (SELECT itemprice_price
                          FROM itemIpsPrice(item_id, cohead_cust_id, cohead_shipto_id,
                                      roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id), (bomitem_qtyfxd + bomitem_qtyper * wo_qtyord) * (1 + bomitem_scrap)),
                                      bomitem_uom_id, bomitem_uom_id, cohead_curr_id, CURRENT_DATE, CURRENT_DATE, cohead_warehous_id) LIMIT 1)
               END AS price
        FROM wo JOIN itemsite ps ON (ps.itemsite_id=wo_itemsite_id)
                JOIN bomitem ON (bomitem_parent_item_id=ps.itemsite_item_id AND
                                 bomitem_rev_id=wo_bom_rev_id AND
                                 woEffectiveDate(wo_startdate) BETWEEN bomitem_effective and (bomitem_expires - 1))
                JOIN itemsite cs ON (cs.itemsite_item_id=bomitem_item_id AND
                JOIN item ON (item_id=cs.itemsite_item_id)
                LEFT OUTER JOIN coitem ON (wo_ordtype='S' AND
                LEFT OUTER JOIN cohead ON (cohead_id=coitem_cohead_id)
        WHERE ( (wo_id=pWoid)
          AND   ((bomitem_char_id IS NULL)
             OR  EXISTS (
                 SELECT charass_id
                 FROM coitem,charass
                 WHERE ((charass_target_type='SI')
                   AND  (charass_target_id=coitem_id)
                   AND  (charass_char_id=bomitem_char_id)
                   AND  (charass_value=bomitem_value)
                   AND  (wo_ordtype='S')
                   AND  (coitem_id=wo_ordid)))) )
        ) AS data"
PL/pgSQL function explodewo(integer,boolean) line 77 at SQL statement
(P0001) QPSQL: Unable to create query

The Query:
SELECT explodeWo(:wo_id, :explodeChildren);

Bound Values:
:explodeChildren:	true
:wo_id:	478