How to exclude Test Users from billing in Atria

How to exclude Test Users from billing in Atria

Overview

In this article, we will show you how to create non-billable users for a customer.  The idea to show non-billable users in Atria is to create billing rules in the service billing Configuration page, we will use a query to retrieve Test users from the database. This way the report will prioritize and run the created rule and will mark the Test Users as non-billable.

Database Configuration

Run SQL Server Management Studio on your Atria database server and connect to your database engine, then add the following table function to your OLM database:
  1. USE [OLM]
  2. GO
  3. SET ANSI_NULLS ON
  4. GO
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  7. Create FUNCTION [dbo].[tf_ExcludeTestUsers]
  8. (
  9. @BillPeriodID int
  10. )
  11. RETURNS @Result TABLE
  12. (
  13. ObjectID INT,
  14. MaxAuditID INT
  15. )
  16. AS
  17. Begin
  18. INSERT INTO @Result (
  19. ObjectID
  20. ,MaxAuditID)
  21. SELECT ObjectID
  22. ,max(AuditID) AuditID
  23. FROM PropertyValues_AT
  24. WHERE PropertyID = (
  25. SELECT PropertyID
  26. FROM Properties p
  27. WHERE p.Name = 'employeetype')
  28. AND ActionDate <= (
  29. SELECT EndDate
  30. FROM BillPeriod
  31. WHERE BillPeriodID = @BillPeriodID)
  32. AND (PropertyValues_AT.Value = 'True')
  33. GROUP BY ObjectID
  34. RETURN 

  36. End

User Configuration

  1. Logon to Atria web portal 
  2. Select a customer by navigating to Customers -> Customers and select a customer, then click on Users
  3. Edit an existing user or create a new user
  4. Click on Additional User Properties and select Test User checkbox as shown below

  5. Provision the user

Billing Reports Configuration

  1. In Atria click on Reports -> Service Billing Configuration
  2. In the Service Billing Rule Configuration Click on New Rule to create a new rule so that you can exclude test users.
  3. Enter a Description and Override Reason
  4. It is important to enter 1 in Processing Order.

  5. Select a value for Valid From and Valid To date
  6. Click on Rule Type Drop-Down box and select Custom Where Clause

  7. To exclude test users in a particular Bill period, enter the following text inside the Where Text box:
    1. BillItems.UserID in (select u.UserID from Users u inner join (select * from dbo.tf_ExcludeTestUsers(@BillPeriodID)) as A on u.ObjectID = a.ObjectID)

  8. Alternatively, to exclude Test Users without any time filtration, enter the following script in the Where Text box:

    1. SELECT BillItems.UserID FROM BillItems WHERE BillItems.UserID IN (SELECT vw.UserId FROM vw_UserUserProperties vw WHERE ((vw.Property ='employeeType' AND (vw.Value ='True'OR vw.Value ='1'))

  9. Click on the Save to add the rule to the Service Billing Rule Configuration
  10. Click on Reprocess Billing Records
  11. Click on Reports -> Service Billing Details to view customer and user services
  12. The test users that were created before will show No under Billable

Notes
If you find the Test User previously set is not shown correctly to the report, uncheck Test User, Provision, and again check Test User and Reprovision the user.