by Marc Applefeld
Bookmark

Retiring Billing and Settlement Rates Using a SQL Job

A client requested a SQL job that would run in the background of their system to retire Billing and Settlement rates within the TMWSuite rate tables. The job would retire rates after a dormant period of inactive use, ranging from a 6 to 12 month time frame. A SQL job was desired to automate the process, since it would be executed on a recurring schedule by the SQL Server Agent.

However, the client wanted to initially review the rates which would be retired manually before the process would become fully automated. We chose to approach this by creating an SSRS (SQL Server Reporting Services) report. The SSRS report SQL scripting would be created for manual execution to display results (and optionally proceed with retiring them via parameters), then later the same SQL scripting could be put into a SQL Job to run automatically.

The SSRS report parameter had two options – Review and Execute, and defaulted to Review. On Review the client will see which rates will be retired, so the list could be evaluated and data edited in TMWSuite before proceeding. On Execute those rates will then be retired. Ultimately when the client was ready for the job to run as an automated process, it would simply involve modifying the SQL script that the SSRS report queries to populate a scheduled task that executes the script. It can also send an emailed report of what rates were retired by creating a separate SQL Job.

    Below is a flow chart to visually recap what was mentioned above.
The report is SSRS based. It can either be Manual or Automatic in execution. Modifying the SQL script that the SSRS report queries determines it's execution type. A separate SQL job can also be created that emails out a report of the retired rates once automated. The email serves the ability to review what was retried, similar to how the Review mode in the manual option allows reviewing before retiring.
The report is SSRS based. It can either be Manual or Automatic in execution. Modifying the SQL script that the SSRS report queries determines it’s execution type. A separate SQL job can also be created that emails out a report of the retired rates once automated. The email serves the ability to review what was retried, similar to how the Review mode in the manual option allows reviewing before retiring.

In either scenario of manual or automatic execution, the best practice is to utilize the SQL code to test for rates and confirm the logic, then use of a test system to confirm retiring rates.

Traditionally a company will retire rates by updating the “date through” on the rate itself. However this shows that custom requests, that are client specific, can still be achieved. Executing updates through a job or report is a tool that can be used very broadly.

    Examples of using this type of approach include:
    1. Reviewing and Releasing Invoices.
    2. Reviewing and Releasing Pay.
    3. Reviewing and Expiring Rates.
    4. Reviewing and Updating Master Files (e.g. Drivers, Customers, etc…).

While this was a specific case scenario, this model can work in numerous ways. This method to update data is particularly handy when there isn’t time or interest in an entire external application to create a workflow.