ReadSoft: How to retrieve the support log from the INVOICES database

Summary

Support Article

At times it may be necessary to go into INVOICES database to retrieve support log information.

To query the support log, run the following query on the SQL database, where <item key> equals the invoice's item key:

SELECT *
FROM xxit3.xxit3_log
WHERE key1 = '<item key>'
ORDER BY key1

To retrieve the InvoiceIT log, run the following query on the SQL database, where <invoice number> equals the invoice number:

SELECT *
FROM xxit3.xxit3_log
WHERE key1 IN (SELECT DISTINCT xi.xxit_item_key
    FROM xxit3_invoice_lines xi
    WHERE xi.invoice_id = (SELECT invoice_id
        FROM xxit3_invoices
        WHERE invoice_num = '<invoice_number>'))
ORDER BY ref#

To retrieve the internal XML from the InvoiceIT Interface tables, run the following query on the SQL database, where <invoice number> equals the invoice number:

FROM xxit3.xxit3_interface
WHERE parent_item_key = (
SELECT Min(xi.xxit_item_key)
    FROM xxit3_invoice_lines xi
    WHERE xi.invoice_id = (SELECT invoice_id
        FROM xxit3_invoices
        WHERE invoice_num = '<invoice_number>'))