xTuple.com xTupleU Blog & News Customer Support

xTuple 5.0 problem with insertinvoicelineitem function

I use the api.invoiceline view to insert imported invoice lines. That view has a “rule” that provides _INSERT functionality. The rule just calls the function defined as "insertinvoicelineitem(new.*). That function has some problem code.

Near the top of the function it checks to see if the invoice header exists. It executes this code:

SELECT invchead_id INTO _check
FROM invchead
WHERE (invchead_id=getInvcheadId(pNew.invoice_number));

I think the query optimizer is having problems here (Posgres 9.6) The query as written never completes on my 290,000 record invchead table.

However this is a simple rewrite of that section:

SELECT gettinvcheadId(pNew.invoice_number) INTO _my_id;

SELECT invchead_id INTO _check
FROM invchead
WHERE (invchead_id= _my_id);
The new query completes in about 100ms.

I’ll make the change on my system but I request that this function be analyzed and modified to include this fix.

Jim

Jim,

Thank you. A fix has been submitted for review. The two queries can be reduced to the following without harm:

 _check := getInvcheadId(pNew.invoice_number)
```

Gil