Can not edit recurring invoice Detail for one of my companies

One of my users receives an error when they attempt an update Detail on a recurring invoice.  Other aspects of the invoice can be updated, and Detail can be updated for the other companies in our system.  This error is triggered on all recurring invoices under this one company.

Parents
  • 0

    It might be an old recurring invoice, or one that was corrupted in an update or in some other manner, but the easiest solution is probably to just recreate the recurring invoice and go on your merry way. There is a missing sales tax reference that is required, even when it is $0, and you are encountering a referential integrity error because of it.

  • 0 in reply to Contefication

    This is occurring on all reoccurring invoices in the one company, so I doubt it would be a bad or corrupted invoice, unless they all managed to corrupt simultaneously. Did any of the recent updates change sales tax settings?

  • 0 in reply to JacksonM

    OP seemed to indicate it was 1 but in reply to your comment, it is a recurring invoice which is stored in separate schema. Not sure if it relates to the invoices created from that one recurring invoice or if it is all recurring invoices in the company. Either way, the raised error indicates a referential integrity problem where the tarRecurInvcLineDs.STaxTranKey value references a row that is not present in the parent tciSTaxTran table. This could occur in a number of ways but the most typical are a UI problem or error during creation or editing of the data. I've also seen this type of problem occur with data purges and cross version logic changes.

    It is not likely to have occurred with a sales tax update but that is always a possibility. Some of this might depend on the version in which the recurring invoices were created or some setting changes as you imply as well. For example, there is potentially a scenario where the recurring invoice(s) was created when the sales tax tracking was deselected and that setting was recently selected. This type of problem is exacerbated across versions where behavior or data relationships can change.

    A data fix might be necessary to repair the recurring invoice sales tax references if the problem is widespread and no other solution can be found.

  • 0 in reply to Contefication

    When I review the SQL tables related to the detail line, on all reoccurring invoices that I have checked, the STaxTranKey value does exist in the STaxTran table. (I work at same company as OP) 

  • 0 in reply to JacksonM

    This query has most of the relationships. You can restrict it however you need. Obviously any rows that are null for tarRecurInvcLineDs down are a problem.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    SELECT
            *
        FROM
            [dbo].[tarRecurInvoice]
            INNER JOIN [dbo].[tarRecurInvcDetl]
                ON [tarRecurInvoice].[RecurInvoiceKey] = [tarRecurInvcDetl].[RecurInvoiceKey]
            LEFT OUTER JOIN [dbo].[tarRecurInvcLineDs]
                ON [tarRecurInvcDetl].[RecurInvcLineKey] = [tarRecurInvcLineDs].[RecurInvcLineKey]
            LEFT OUTER JOIN [dbo].[tciSTaxTran]
                ON [tarRecurInvcLineDs].[STaxTranKey] = [tciSTaxTran].[STaxTranKey]
            LEFT OUTER JOIN [dbo].[tciSTaxCodeTran]
                ON [tciSTaxTran].[STaxTranKey] = [tciSTaxCodeTran].[STaxTranKey]
            LEFT OUTER JOIN [dbo].[tciSTaxCodeCompany]
                ON [tciSTaxCodeTran].[STaxCodeKey] = [tciSTaxCodeCompany].[STaxCodeKey];

Reply
  • 0 in reply to JacksonM

    This query has most of the relationships. You can restrict it however you need. Obviously any rows that are null for tarRecurInvcLineDs down are a problem.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    SELECT
            *
        FROM
            [dbo].[tarRecurInvoice]
            INNER JOIN [dbo].[tarRecurInvcDetl]
                ON [tarRecurInvoice].[RecurInvoiceKey] = [tarRecurInvcDetl].[RecurInvoiceKey]
            LEFT OUTER JOIN [dbo].[tarRecurInvcLineDs]
                ON [tarRecurInvcDetl].[RecurInvcLineKey] = [tarRecurInvcLineDs].[RecurInvcLineKey]
            LEFT OUTER JOIN [dbo].[tciSTaxTran]
                ON [tarRecurInvcLineDs].[STaxTranKey] = [tciSTaxTran].[STaxTranKey]
            LEFT OUTER JOIN [dbo].[tciSTaxCodeTran]
                ON [tciSTaxTran].[STaxTranKey] = [tciSTaxCodeTran].[STaxTranKey]
            LEFT OUTER JOIN [dbo].[tciSTaxCodeCompany]
                ON [tciSTaxCodeTran].[STaxCodeKey] = [tciSTaxCodeCompany].[STaxCodeKey];

Children
No Data