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')


    • Related Articles

    • Audit Scripts: Row Counts and Progress Monitoring

      This article shows you a script that can be used to monitor the progress of both a Backup and a Restore. It can also be used to just review row counts on all the Universal Database tables. The first code block is used to populate a SQL Server Temp ...

    • Audit Scripts: General Audit

      After completing a backup there are a number of things that are good to review prior to restoring into the destination system. The script below reviews several of the data points that should be looked at prior to restoring. This script is not ...

    • Other: Total Record Counts

      The following SQL script will count all records in all tables in a database. DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ; INSERT INTO @TableRowCounts ([TableName], [RowCount]) EXEC sp_MSforeachtable 'SELECT ''?'' ...

    • Other: Obtaining Record Counts for all Tables

      The following script will count all records in all tables. DECLARE @TableRowCounts TABLE ( [TableName] VARCHAR(128), [Batch_Number] INT, [Incomplete] INT, [Complete] INT, [Total] INT ) ; INSERT INTO @TableRowCounts ( [TableName], [Batch_Number], ...

    • Other: Creating Unique Display Numbers

      The following script will create unique Final_ReferenceCode for all Contacts. It can be easily modified to apply to other record types. Please note that the Final_ReferenceCode that are generated are based on the data in the database. If you are ...