xTuple.com xTupleU Blog & News Customer Support

Poitem_itemsite_id in poitem table

I notice from the xTuple source that you insert the poitem_itemsite_id column into the poitem table.
When I was testing changing the item for an existing poitem; I received an error saying

SQL Error [P0001]: ERROR: You may not change the item site for a line item.
Where: PL/pgSQL function _poitemtrigger() line 156 at RAISE

So I went back to check your update statement and noticed that you DO NOT update the poitem_itemsite_id column.

Why do you insert but not update?

Is this column obsolete?

Thank you



The poitem_itemsite_id is still very much alive and necessary. The restriction on updating it was added in 2008 as part of creating the API view api.purchaseline. This is the first I’ve heard of this restriction being a problem.

We’ll need to talk internally about the consequences before removing this limitation.



The flow on that screen is that once the line item has been saved (the record is inserted at po line item save) it can’t be changed. If you insert it no longer can be updated. The itemsite_id is the reference to the item and the corresponding site. While is true that can’t affect planning systems until the PO is release and may be possible to update it while PO is still open, changing that will need some internal review with the team.


for now I commented out the code in the function public._poitemtrigger

IF (NEW.poitem_itemsite_id != OLD.poitem_itemsite_id) THEN
– RAISE EXCEPTION ‘You may not change the item site for a line item.’;

I tried to override the function in our custom schema, but it would not run the custom function.

I created a set of custom screens called tradePO which is a little different that the standard PO that xTuple uses. For one thing, the vendor is specified at the PR stage, another customization. And then the items are added in as required.

My concern was that people make mistakes and need to change the item. They will be annoyed to have to delete the item and then enter it back in.

and since we dont require inventory, my change should be OK.

A trigger in the custom schema attached to the main table works in parallel, it will not overlap. Is valid to add triggers to tables in a different schema while your function remains in your schema (in my opinion). The only way to bypass that trigger is to modify the original trigger function. I will not recommend that you make that comment on that line because will open the door to modify an open PO. It may be better to not raise if the PO head status is Open.


thank you Alfredo, will do