Audit Scripts: Activity Billed/Unbilled row counts

Audit Scripts: Activity Billed/Unbilled row counts

After you have backed up financial information like Time Entries, Expense Entries, and Fee Entries it is a good idea to audit the results prior to restoring data to the destination system.  For example, some Universal Destinations support restoring unbilled Work In Progress (WIP), but not activities that have been previously billed.  Therefore, ensuring that the invoiced status was backed up correctly is very important.

The below query is a quick audit to confirm that the invoiced status of Time, Expense, and Fee activities were backed up correctly.

  1. SELECT
  2.     '__M_Financial_Activities_Expenses' tableName,
  3.     IIF(Final_ArInvoice_Id = '', 'Unbilled', 'Billed') billedStatus,
  4.     COUNT(*)
  5. FROM
  6.     __M_Financial_Activities_Expenses
  7. WHERE 1=1
  8.     AND Final_Status != 'Deleted'
  9. GROUP BY
  10.     IIF(Final_ArInvoice_Id = '', 'Unbilled', 'Billed')

  12. UNION

  14. SELECT
  15.     '__M_Financial_Activities_Fees' tableName,
  16.     IIF(Final_ArInvoice_Id = '', 'Unbilled', 'Billed') billedStatus,
  17.     COUNT(*)
  18. FROM
  19.     __M_Financial_Activities_Fees
  20. WHERE 1=1
  21.     AND Final_Status != 'Deleted'
  22. GROUP BY
  23.     IIF(Final_ArInvoice_Id = '', 'Unbilled', 'Billed')

  25. UNION

  27. SELECT
  28.     '__M_Financial_Activities_Time' tableName,
  29.     IIF(Final_ArInvoice_Id = '', 'Unbilled', 'Billed') billedStatus,
  30.     COUNT(*)
  31. FROM
  32.     __M_Financial_Activities_Time
  33. WHERE 1=1
  34.     AND Final_Status != 'Deleted'
  35. GROUP BY
  36.     IIF(Final_ArInvoice_Id = '', 'Unbilled', 'Billed')