Hi,
I like to directly query the IP database to pull data that is helpful in our business.
I have created a query that summarizes the amounts we have invoiced, been paid, and an outstanding amount, all grouped by yearly quarters.
The problem I have is that the outstanding amount becomes ‘tainted’ from invoices that we issue, but for various reasons, will not be paid in full. Sometimes customers cancel on us and we don’t like to remove unpaid or partially paid invoices from our system just so that we can maintain as much data integrity as possible. (It’s also useful for more business data and statistics etc. percentage rates of cancellations for example)
So we use the Draft status of an invoice to signify that the job was cancelled, or ended abnormally. If an invoice is Sent or Viewed, we know it is still outstanding and pending. If the invoice is Paid, we know it is finalized and paid in full.
I want to alter my SQL query to look at an invoice status and if it’s set to Draft, then make the query act accordingly.
I found in ip_invoices there is the field called: invoice_status_id which is an integer value. Is there somewhere that relates those integers to the human readable values? I’d prefer to use the human readable value in my SQL because it will make it clearer in the future what is happening in the query.