xTuple.com xTupleU Blog & News Customer Support

Price Schedules - Updating

I want to update price schedules by category and round to the nearest dollar.

Is the function involved public.updatepricesbyproductcategory? If so, I cannot find the table “ipsitem” (I do see “ipsiteminfo”). How does function work if not calling correct table?

I forced the _currencyDecimals INTEGER :=0; and it still does not round.

Any help would be greatly appreciated.

I’m not sure that function is in use. The screens that update prices, including List Price updates, by Product Category and by Schedule all use metasql statements that update the ipsiteminfo table directly. I believe there used to be a sql view named ipsitem, which supplied backward compatibility for several versions of xTuple.

You actually never mentioned price schedules, but the ips tables are for schedules. If you’re dealing with Product Category in the price schedules, then you wouldn’t be setting the price, but setting a discount. If you have item pricing in your pricing schedules, but still are looking to update by product category, then you’d have to include the item table or API view in your update.

If you’re attempting to set list prices by product category, you’d do the following:

Using API views:
UPDATE api.item SET list_price = round(list_price) WHERE product_category = ‘MYAWESOMESTUFF’;

Using the table:
UPDATE item SET item_listprice = round(item_listprice) WHERE item_prodcat_id = getprodcatid(‘MYAWESOMESTUFF’)

If you are trying to update the pricing schedules by product category, you’d do the following:

Using API views:
UPDATE api.pricingscheduleitem SET price = round(price) WHERE item_number IN
(select item_number from api.item WHERE product_category = ‘MYAWESOMESTUFF’)

Using the table:
UPDATE ipsiteminfo SET ipsitem_price = round(ipsitem_price) WHERE ipsitem_item_id IN
(SELECT item_id FROM item WHERE item_prodcat_id = getprodcatid(‘MYAWESOMESTUFF’))

It should go without saying that you need to be careful doing this. I ALWAYS perform a SELECT before executing an UPDATE.

Good luck
Scott

Scott,
Thanks for the quick reply. Just the direction I needed. I found the Update metasql and modified succesfully. Thanks again!!