Menu

Mass update agreement billing rates in ConnectWise

Google+0
LinkedIn0

As I discussed last week’s post about updating ConnectWise agreement exclusions, agreement types are a useful way of ensuring conformity across your portfolio of agreements, but changes made to agreement types don’t propagate down to individual agreements. This means that if you need to update the agreement billing rates, for example, you’re left with a lot of manual work.

The good news is that you can use the same approach in SQL as I illustrated last week to perform a mass update of the custom work roles and work types you’ve attached to agreement types. Here’s how:

Step 1 — Update the exclusions on your agreement types

The first thing you’ll need to do is make sure all of your agreement types are configured with the appropriate custom work roles and work types. Navigate to Setup > Setup Tables > Agreement Types and use the Work Role and Work Type tabs to do this.

Repeat this process for all of the agreement types you’d like to update agreement billing rates for.

Step 2 — Make a list of the agreement types you’d like to update

Next, you’ll need to retrieve the AGR_Type_RecIDs for each of the agreement types you updated in step 1. This query returns all of the active agreement types in ConnectWise along with their unique record id:

SELECT     AGR_Type_Desc, AGR_Type_RecID
FROM         dbo.AGR_Type
WHERE     (AGR_Inactive_Flag = 0)
ORDER BY AGR_Type_Desc

Here is an example of what SQL will return:

AGR_TYPE_DESCAGR_TYPE_RECID
BDR12
Block Time5
Gold42
Platinum41
Silver43

For our example, we’ll be working with Gold (42), Platinum (41) and Silver (43).

Step 3 — Expire existing custom agreement work roles

Before you populate custom work roles from agreement types to individual agreements, you’ll need to set all of the currently active work roles attached to agreements to expire:

UPDATE    Company_Billing_Rate
SET              Date_Ending = '03/31/2012'
WHERE     (Date_Ending IS NULL) AND (Date_Effective < '03/31/2012') AND (AGR_Header_RecID IN
                          (SELECT     AGR_Header_RecID
                            FROM          dbo.AGR_Header
                            WHERE      (AGR_Cancel_Flag = 0) AND (AGR_Date_End > GETDATE()) AND (AGR_Type_RecID IN (41, 42, 43)) OR
                                                   (AGR_Cancel_Flag = 0) AND (AGR_Type_RecID IN (41, 42, 43)) AND (AGR_NoEnd_Flag = 1))) AND (Date_Ending IS NULL)

Update lines 2 and 3 with the date you’d like the existing custom work roles to end. For example, if your new work roles go into effect on 4/1/2012, then perhaps you select the previous day, 3/31/2012. Update lines 6 and 7 (in both places) with the list of AGR_Type_RecIDs you gathered during step 2 (in this example, Gold, Platinum, and Silver agreement types).

Step 4 — Expire existing custom agreement work types

Similar to previous step, now you’ll set all of the currently active work types attached to agreements to expire:

UPDATE    Company_Activity_Type
SET              Date_Ending = '03/31/2012'
WHERE     (Date_Ending IS NULL) AND (Date_Effective < '03/31/2012') AND (AGR_Header_RecID IN
                          (SELECT     AGR_Header_RecID
                            FROM          dbo.AGR_Header
                            WHERE      (AGR_Cancel_Flag = 0) AND (AGR_Date_End > GETDATE()) AND (AGR_Type_RecID IN (41, 42, 43)) OR
                                                   (AGR_Cancel_Flag = 0) AND (AGR_Type_RecID IN (41, 42, 43)) AND (AGR_NoEnd_Flag = 1))) AND (Date_Ending IS NULL)

Update lines 2 and 3 with the date you’d like the existing custom work types to end. Update lines 6 and 7 (in both places) with the list of AGR_Type_RecIDs you gathered during step 2.

Step 5 — Populate custom agreement work roles with agreement billing rates

Now that you’ve set all of the old work roles to expire, you can populate the custom agreement work roles you updated in step 1:

INSERT INTO Company_Billing_Rate
                      (Owner_ID, Company_RecID, Date_Effective, Last_Update, Updated_By, Rate_Type, Activity_Class_RecID, AGR_Header_RecID, AGR_Limit, Hourly_Rate)
SELECT     1 AS Owner_ID, AGR_Header.Company_RecID, '04/01/2012' AS Date_Effective, GETDATE() AS Last_Update, 'sqlscript' AS Updated_By, AGR_Type_WRole.Rate_Type,
                      AGR_Type_WRole.Activity_Class_RecID, AGR_Header.AGR_Header_RecID, NULL AS AGR_Limit, AGR_Type_WRole.Rate AS Hourly_Rate
FROM         AGR_Header LEFT OUTER JOIN
                      AGR_Type_WRole ON AGR_Header.AGR_Type_RecID = AGR_Type_WRole.AGR_Type_RecID
WHERE     (AGR_Header.AGR_Type_RecID IN (41, 42, 43)) AND (AGR_Header.AGR_Date_End IS NULL OR
                      AGR_Header.AGR_Date_End > '03/31/2012') AND (AGR_Header.AGR_Cancel_Flag = 0)

Update the dates in lines 3 and 8 to reflect the expiration and effective dates, as discussed in step 3. Update line 7 with the list of AGR_Type_RecIDs you gathered during step 2.

Step 6 — Populate custom agreement work types

Similar to the previous step, now you can populate the custom agreement work types you updated in step 1:

INSERT INTO Company_Activity_Type
                      (Owner_ID, Company_RecID, Hours_Min, Rate_Type, Rate, Last_Update, Updated_By, Hours_Max, Billable_Flag, Activity_Type_RecID, AGR_Header_RecID,
                      Date_Effective, Date_Ending)
SELECT     1 AS Owner_ID, AGR_Header.Company_RecID, AGR_Type_WType.Hours_Min, AGR_Type_WType.Rate_Type, AGR_Type_WType.Rate, GETDATE() AS Last_Update,
                      'sqlscript' AS Updated_By, AGR_Type_WType.Hours_Max, 1 AS Billable_Flag, AGR_Type_WType.Activity_Type_RecID, AGR_Header.AGR_Header_RecID,
                      '04/01/2012' AS Date_Effective, NULL AS Date_Ending
FROM         AGR_Header INNER JOIN
                      AGR_Type_WType ON AGR_Header.AGR_Type_RecID = AGR_Type_WType.AGR_Type_RecID
WHERE     (AGR_Header.AGR_Type_RecID IN (41, 42, 43)) AND (AGR_Header.AGR_Date_End IS NULL OR
                      AGR_Header.AGR_Date_End > '03/31/2012') AND (AGR_Header.AGR_Cancel_Flag = 0)

Update the dates in lines 6 and 10 to reflect the expiration and effective dates, discussed in step 3. Update line 9 with the list of AGR_Type_RecIDs you gathered during step 2.

That’s it. Once you have these SQL scripts customized for your environment and particular use, you can re-run them any time you need to mass update the custom work roles and work types you have attached to an agreement.

Want to do more?

Congratulations! You’ve mass-updated your agreement billing rates. Do you want to do more of your own ConnectWise reporting in Report Writer, Power BI, or another reporting tool? Contact usfor one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.

Related Posts