xTuple.com xTupleU Blog & News Customer Support

Problem with _soheadtrigger() in Xtuple 5.0

New Development
#1

I just wanted to report an issue with the _soheadtrigger() that prevents me from using the api.salesorder view (or cohead for that matter) to insert data in certain cases. I’ve reproduced the operation within the xt5 masterref database.

Steps to reproduce in masterref. Go to the TTOYS customer and on the Addresses tab select “Ship to” and disable the “Default” attribute from any of the stored ship to addresses.

Execute this query:
" INSERT INTO api.salesorder(order_number, order_date, customer_number, sale_type,
shipto_name, shipto_address1, shipto_address2, shipto_city,
shipto_state, shipto_country, shipto_postal_code,
shipto_shipzone, freight, shipping_chgs, ship_via,
shipto_phone, shipto_contact_phone, shipto_contact_email, hold_type, tax_zone,
cust_po_number)
VALUES(‘999999’,‘2019-09-24’, ‘TTOYS’, ‘Phone’,
‘SHIP TO NAME’,’ Shipto Addr1’, ‘Shipto Addr2’, ‘Shipto City’,
‘OK’, ‘US’, ‘74074-2345’,
‘DOMESTIC1’,1.0, ‘ADDCHARGE’,‘UPS-GROUND’,
‘405-377-6570’,‘405-377-6570’, ‘jwirt@beardon.com’,‘Credit’, ‘VA Sales Tax’,
‘xxy345’) ;"

The order is inserted with no line items so you’ll get an error regarding that fact when you open the order to view.
The problem is that none of the shipto address information is imported.

Looking at the code in the trigger function it looks like around Line#145 all the passed in shipto address values are being overwritten. This effectively eliminates the ability to do freeform shipto addresses. I’ve modified the trigger to get it to work for us but I’m hoping that the _soheadtrigger function can be reviewed and tested and changed if that is not the way it was intended to function.

Thanks.
Jim

#2

If you look at that trigger function it is checking if a Ship To ID is being passed in. If there is a Ship To ID then it will use that, otherwise it will use the defaults from the customer record (as you suggested around line 145).

If you look at the API.salesorder view there is logic to determine the ship to ID from a combination of customer number and shipto number.

However, as you describe, if you pass in a customer/shipto combination that does not already exist in the system, there will be no ship to ID and the system will use defaults as you suggest.

It would seem that an additional “Use Free Form Ship To” setting is needed in the API view so that is also checked in the trigger function and uses the API shipto information rather than the defaults.

I guess the question is, should we create a new shipto as part of this process as well?

I will raise this as an issue in the system to be looked further into.

#3

Dave,
I really haven’t looked much at the api.salesorder view but I get the same results if I do the insert directly into cohead and send the shipto fields along with the values required to fulfill the foreign key requirements of a record. I see that the trigger DOES look at the cust_ffshipto value and responds to that setting but that is AFTER it has already overwritten for example “NEW.cohead_shiptoaddr1”. The way the trigger works I don’t see any way that it will populate for example the newly inserted record’s ( for example) cohead_shiptoaddr1 with the value I use in the “INSERT” statement. My understanding is that is what “free form” means. I doesn’t have to be created as a CRM record. It is just contained in the cohead record.
My customer’s use case is for example to insert sales orders (cohead) via an API from an external shopping site. All the shipto address information is entered by customers and they don’t want to bloat the CRM system by adding an address record for each order. They also use an API to import orders that were placed on Amazon’s store. They don’t want thousands of SHIPTO records under the Amazon account. The previous behavior worked very well. The only places those addresses were needed was within the cohead record.
I may not be communicating my need very clearly. So that we can be on the same page, how would you accomplish an insert that would allow you to create a cohead record for the masterreff TTOYS customer and have the freeform SHIPTO be only stored it the cohead record?

Jim

#4

Jim,

Yes - I guess I was saying the same thing as you. I got to the part where the default override is occurring and stopped there.

The API view does not have the free-form shipto setting available to set, and the trigger does not consider it when checking for default or otherwise.

Seems like the problem is well understood, just needs to be resolved. I have raised an issue to get prioritised and resolved.

The problem will be resolved with changes to the API view and associated rules, plus the soheadtrigger function, so could be applied to a system ahead of the next xTuple release.

#5

Dave,
I might be reading the trigger wrong but I think that _soheadtrigger DOES consider the free-form shipto in its logic. It is included in the SELECT at line 66. The cust_ffshipto is tested both at line ~ 349 and 404. However those test don’t do anything since the settings in the NEW.cohead_shipto fields have gotten wiped out up at line 145.

Jim

#6

Basically all the checks in the soheadtrigger function are only considering whether the NEW.cohead_shipto_id IS NULL. For free form ship to addresses in your scenario this will always be the case and so the trigger will always apply the customer default.

What needs to happen is that check needs to also consider the customer freeform shipto flag and to use the addresses provided if the flag is true. So yes, you are probably correct in that the API views do not need to change, but the checks in the trigger absolutely have to.

#7

Agreed. I’ve just modified the existing trigger code to prevent the overwrite of my passed in values. I just wanted to get some of developers to take a look at it and see if that was the intended behavior so it could be changed in the released code and wouldn’t be replacing my modifications the next time we upgrade. Thanks Dave.

Jim

#8

Changing line 142 of that function to

      IF (NEW.cohead_shipto_id IS NULL AND NOT _p.cust_ffshipto) THEN

appears to resolve the problem