Custom Bill Processing Stored Procedure

Overview

Bill Processing is a data task that collates and aggregates data into a format that can be used for billing.

In some cases, individual services may have additional billing requirements that are not catered for by the default bill process. Atria will by default handle items that are assigned via Plan to customers or users, but it does not cater for billable items that are not catered for in the standard service model structure.

For example:

• A backup service may need to bill based on storage consumed by each customer.
• A networking service may bill based on bandwidth consumed.
• An SMS system may bill based on messages sent.

To support these situations Atria can automatically execute customized stored procedures as part of the default billing process, this provides a simple means for you to enrich your billing data and automate additional billing processes.

Process

1. Decide how you want this to appear on a Customer Invoice: Is this stored at customer level, or do you have data at User level? This will determine what data you need, and how it will need to be aggregated.

2. Transfer associated billing data into Atria: You must have the data you need available from the Atria database. You can use Atria Jobs to execute scheduled tasks to import data via PowerShell.

3. Build Stored Procedure following the template: The structure of the Stored Procedure needs to tightly follow the guidelines outlined below.

4. Test Atria will delete all data associated with a billing period each time the bill processing is run. To test your code, execute the Bill Processing Job from the Atria Jobs interface.

Stored Procedure Structure

Atria will attenmpt to execute a Stored Procedure that is named according to the following pattern, these are executed at the END of the standard billing process.

[{schema}.]sp_BillProcessing__{ServiceName}_{CustomName}

The main Atria sp_BillProcessing stored procedure looks for stored procedures that match this pattern.

Procedures will be executed in alphanumerical order, so if ordering is important, make sure the {CustomName} portion of the pattern is named according to the order that you wish these procs to be processed.

warning

Do not omit the _{CustomName}. the format [{schema}.]sp_BillProcessing_{ServiceName} is used for system generated Stored Procedures, and will likely overwrite any custom procs using this format.

Structure

To generically run these stored procedures and to use the output successfully, the procedures must accept the same input parameters, and must output data in the same format. To accommodate this, use the following structure:

CREATE Proc [{schema}.]sp_BillProcessing_{ServiceName}_{order}_{CustomName}
    @PeriodStartDate datetime,
    @PeriodEndDate datetime,
    @BillPeriodId int
 as
SET NOCOUNT ON
 
set transaction isolation level read uncommitted
BEGIN TRAN

CREATE TABLE #{TempTable} (
    [BillItemID] [int] IDENTITY(1,1) NOT NULL Primary Key Clustered,
    [ServiceChangeID] [int] NULL,
    [ObjectID] [int] NULL,
    [ObjectTypeID] [int] NULL,
    [CustomerID] [int] NULL,
    [UserID] [int] NULL,
    [UPN] [nvarchar](100) NULL,
    [UserFullName] [nvarchar](128) NULL,  
    [Username] [nvarchar] (64) NULL,
    [ServiceID] [int] NULL,
    [ServiceCode] [nvarchar](64) NULL,
    [ServiceName] [nvarchar](64) NULL,
    [InstanceName] [nvarchar](64) NULL,
    [ProductSKU] [nvarchar](100) NULL,
    [PlanCode] [nvarchar](64) NULL,
    [PlanName] [nvarchar](64) NULL,
    [ServiceItemID] [int] NULL,
    [PlanID] [int] NULL,
    [LineDescription] [nvarchar](200) NULL,
    [Quantity] [decimal](10, 4) NULL Default (1),
    [Billable] [bit] NOT NULL Default(1),
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [ChangeType] [char](1) NULL,
    [AddedByUserID] [int] NOT NULL,
    [AddedByUserName] [nvarchar](50) NOT NULL,
    [RemovedByUserID] [int] NULL,
    [RemovedByUserName] [nvarchar](50) NULL,
    [SummaryGrouping] [nvarchar](200) NULL
)
-- Insert Period Data into Temp Table
-- *** <add your queries to gather data and insert into the temp table defined above>***

-- *** <Update any Customer, Service, and User related values>***
-- *** <Any queries needed to update related columns>***
 
 -- Output data - returns data in expected format.
Select  [ServiceChangeID],[ObjectID],[ObjectTypeID],[CustomerID],
        [UserID],[UPN],[UserFullName],[Username],
        [ServiceID],  [ServiceCode],[ServiceName],[InstanceName],
        [ProductSKU],[PlanCode],[PlanName],[ServiceItemID],[PlanID],[LineDescription],[Quantity],[Billable],
        [StartDate],[EndDate],[ChangeType],
        [AddedByUserID],[AddedByUserName],[RemovedByUserID],[RemovedByUserName],[SummaryGrouping]
from #{TempTable}

IF (@@ERROR <> 0) BEGIN
    -- *** Customise exception below ***
    RAISERROR ('Failed to Process ***Add name of procedure here*** Billing Records',16,-1)
    ROLLBACK TRANSACTION
END ELSE BEGIN
    COMMIT TRANSACTION
END
GO

• The first 41 lines of the above sample should pretty much remain unchanged to ensure the procedure:
  • does not return record count progress messages
  • is run within a transaction
  • has access to non-committed data
  • is working with a temp table structure consistent with what is needed as output to the main processing procedure
• Additional columns can be added to the Temp table if needed, but they cannot be used in the final select statement
• The sections in green comments surrounded by *** are the areas that should be customized per procedure
• The Output Data statement (SELECT) and finalise transaction statements must also remain unchanged (with the exception of the #{TempTable} name).

Patterns

The standard Bill Processing process uses patterns to ensure consistency for the LineDescription and SummaryGrouping values. There are 2 tables that define these:

• BillItemPatterns
• BillItemPatternTypes

The SummaryGrouping column is important as this is used for aggregation of data, if this is inconsistent then data may not be aggregated as desired when viewed through Atria or pushed onto an invoicing system.