Salesforce Custom Hours Billed Quote with Conga Composer

For a recent client project working with the Salesforce.com platform I used Conga Composer to create custom quotes. The client is an engineering firm and one of the quotes listed employee hours billed under dynamic categories. Here’s how to create the quote.

1. Create a report table in Salesforce using the schema builder. Give the table 10 or so text columns, and an identifier column that will store the object id of the associated Salesforce Opportunity object (i.e. a key in normal database nomenclature).

Salesforce Report Object

2. Create a hours master-detail object in Salesforce using the schema builder. In my example each hours billed will be grouped by a project role and part.

Salesforce Hours Object

3. Create an APEX trigger that will fill the report table each time an hours item is added to our opportunity. I keep my APEX logic code in individual classes with a single APEX trigger – this keeps everything clean and easy to debug if you have multiple APEX triggers that are order dependent.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
trigger UpdateHourlyFeeTotal on Opportunity_Hours_Item__c (after insert, after update) {

  // call class static method to compute total, pass in opportunity id  
  for(Opportunity_Hours_Item__c objLineItem : Trigger.new) {
    Id opportunityId = objLineItem.Opportunity__c;
    clsUpdateOpportunityTotals.updateOpportunityHourlyFeeTotal(opportunityId);
  }
 
  // create report(s)
  Id prevOpportunityId = null;
  for(Opportunity_Hours_Item__c objLineItem : Trigger.new) {
    Id opportunityId = objLineItem.Opportunity__c;
   
    //  create opportunity hourly fee report
    if(opportunityId != prevOpportunityId)
      clsCreateReports.createOpportunityHourlyFeeLineItems(opportunityId);
     
    prevOpportunityId = opportunityId;
  }
 
}

public with sharing class clsUpdateOpportunityTotals {

  public static boolean doNotUpdate{get;set;}
   
  public static void updateOpportunityHourlyFeeTotal(Id opportunityId) {
    // do not recursively update total
    if(doNotUpdate == true) {
      System.debug(Logginglevel.DEBUG, 'updateOpportunityHourlyFeeTotal do not recursively update total');
      return;
    }
       
    // do not call any other triggers... this function is called on opportunity and opportunity
    // hourly fee line item after insert/update
    doNotUpdate = true;
   
    if(opportunityId == null) {
      System.debug(Logginglevel.DEBUG, 'updateOpportunityHourlyFeeTotal bad Id paramater');
      return;
    }
   
    System.debug(Logginglevel.DEBUG, 'updateOpportunityHourlyFeeTotal start');
   
    // get all opportunity hourly fee line items
    List<Opportunity_Hours_Item__c> lstOppHourlyFeeItems = [SELECT Id, Total__c FROM Opportunity_Hours_Item__c WHERE Opportunity__c = :opportunityId];
    if(lstOppHourlyFeeItems.size() < 1) {
      // no hourly fee items
      System.debug(Logginglevel.DEBUG, 'no hourly fee line items');
      return;
    }
   
    System.debug(Logginglevel.DEBUG, 'updateOpportunityHourlyFeeTotal found line items ' + lstOppHourlyFeeItems.size());
   
    // add totals
    Double dblTotal = 0;
    for(Opportunity_Hours_Item__c objLineItem : lstOppHourlyFeeItems) {
      Double dblLineTotal = objLineItem.Total__c;
      System.debug(Logginglevel.DEBUG, 'updateOpportunityHourlyFeeTotal found line item with total ' + dblLineTotal);
      dblTotal = dblTotal + dblLineTotal;
    }
   
    // get opportunity
    Opportunity objOpportunity = [SELECT Id, Hourly_Fee_Line_Item_Total__c FROM Opportunity WHERE Id = :opportunityId];
    if(objOpportunity == null) {
      // no opportunity item
      System.debug(Logginglevel.ERROR, 'no opportunity in database');
      return;
    }
   
    System.debug(Logginglevel.DEBUG, 'updateOpportunityHourlyFeeTotal update opportunity total');
   
    // update opportunity total
    objOpportunity.Hourly_Fee_Line_Item_Total__c = dblTotal;
    update objOpportunity;
   
    doNotUpdate = false;
  }  
}

public with sharing class clsCreateReports {

  public static void createOpportunityHourlyFeeLineItems(Id opportunityId) {
   
    // delete existing report
    List<ZDReport__c> lstReportRows = [
       SELECT
         Id
       FROM
         ZDReport__c
       WHERE
         (Report_Type__c = 'OppHourlyFeeLineItemRoles' OR Report_Type__c = 'OppHourlyFeeLineItemFees')
         AND ObjectID__c = :opportunityId
    ];
    if(lstReportRows.size() > 0) {
       
        // delete each row
        for(ZDReport__c reportRow : lstReportRows)
            delete reportRow;
       
    }
   
    // get all opportunity hourly fee line items
    List<Opportunity_Hours_Item__c> lstOppHourlyRoleItems = [
       SELECT
         Id,
         Total__c
       FROM
         Opportunity_Hours_Item__c
       WHERE
         Opportunity__c = :opportunityId
    ];
   
    if(lstOppHourlyRoleItems.size() > 0) {
       
        // AggregateResult is of class sobject
        // http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_System_SObject_instance_methods.htm
       
        // create rows for each role and pay rate
        AggregateResult[] arOppHourlyFeeRolesRates = [
          SELECT
            Role__c,
            MAX(Hourly_Fee__c) sumHourlyFee,
            SUM(Hours_Worked__c) sumHoursWorked,
            SUM(Total__c) sumTotal
          FROM
            Opportunity_Hours_Item__c
          WHERE
            Opportunity__c = :opportunityId
          GROUP BY
            Role__c
          ORDER BY
            Role__c ASC
        ];
       
        // insert each role
        for(AggregateResult reportRow : arOppHourlyFeeRolesRates) {
           
            // get role and max fee/rate
            String strRole = (String) reportRow.get('Role__c');
            String strFee = String.valueOf((Double) reportRow.get('sumTotal'));
           
            // insert row
            ZDReport__c objZDReportRow = new ZDReport__c(
               ObjectID__c = opportunityId,
               Report_Type__c = 'OppHourlyFeeLineItemRoles',
               Col1__c = strRole,
               Col2__c = strFee
            );
            insert objZDReportRow;
           
        }
       
        // *** insert each part
       
        Integer part = 1;
        Double role = .1;
       
        // create rows for each part and pay rate
      AggregateResult[] arOppHourlyFeeParts = [
        SELECT
          Part__c,
          SUM(Hours_Worked__c) sumHoursWorked,
          SUM(Total__c) sumTotal
        FROM
          Opportunity_Hours_Item__c
        WHERE
          Opportunity__c = :opportunityId
        GROUP BY
          Part__c
        ORDER BY
          Part__c ASC
      ];
           
      for(AggregateResult reportPart : arOppHourlyFeeParts) {
       
        // get part, hours and total
        String strDescription = (String) reportPart.get('Part__c');
        String strHours = String.valueOf((Double) reportPart.get('sumHoursWorked'));
        String strTotal = String.valueOf((Double) reportPart.get('sumTotal'));
        String strPart = '' + part;
       
        part = part + 1;
               
        // insert row
        ZDReport__c objZDReportRowPart = new ZDReport__c(
           ObjectID__c = opportunityId,
           Report_Type__c = 'OppHourlyFeeLineItemFees',
           Col1__c = strPart,
           Col2__c = '', // role
           Col3__c = strDescription,
           Col4__c = '', // rate
           Col5__c = strHours,
           Col6__c = '', // role fee
           Col7__c = strTotal
        );
        insert objZDReportRowPart;
       
        // get roles for each part
        AggregateResult[] arOppHourlyFeeRoles = [
            SELECT
                  Role__c,
                  SUM(Hours_Worked__c) sumHours,
                  MAX(Hourly_Fee__c) maxFee,
                  SUM(Total__c) sumTotal
            FROM
              Opportunity_Hours_Item__c
            WHERE
              Opportunity__c = :opportunityId
              AND Part__c = :strDescription
                GROUP BY
                  Role__c
                ORDER BY
                  Role__c ASC
        ];        
               
        role = 0.1;
               
        for(AggregateResult reportRole : arOppHourlyFeeRoles) {
       
          strDescription = (String) reportRole.get('Role__c');
          String strHoursRole = String.valueOf((Double) reportRole.get('sumHours'));
          String strRateRole = String.valueOf((Double) reportRole.get('maxFee'));
          String strTotalRole = String.valueOf((Double) reportRole.get('sumTotal'));
         
          double tempRole = part + role;
          String strRole = '' + tempRole;
         
          if(role < 0.9)
            role = role + .1;
          else if(role < 0.99)
            role = role + 0.01;
          else
            role = role + 0.001;
       
            // insert row
            ZDReport__c objZDReportRowRole = new ZDReport__c(
             ObjectID__c = opportunityId,
               Report_Type__c = 'OppHourlyFeeLineItemFees',
               Col1__c = '', // part
               Col2__c = strRole,
               Col3__c = strDescription,
               Col4__c = strRateRole,
               Col5__c = strHoursRole,
               Col6__c = strTotalRole,
               Col7__c = '' // part fee
            );
            insert objZDReportRowRole;
       
        }
       
      }        
       
    }
     
  }
 
}

4. Create Conga queries to pull the report data into a quote. This requires installing the Conga Query Manager package in addition to Conga Composer.

1
2
3
4
5
SELECT Group__c, Hourly_Fee__c, Hours_Worked__c, Title__c, Total__c FROM Opportunity_Hours_Item__c WHERE Opportunity__c = '{pv0}' ORDER BY Part__c ASC

SELECT Col1__c, Col2__c FROM ZDReport__c WHERE Report_Type__c = 'OppHourlyFeeLineItemRoles' AND ObjectID__c LIKE '{pv0}%'

SELECT Col1__c, Col2__c, Col3__c, Col4__c, Col5__c, Col6__c, Col7__c FROM ZDReport__c WHERE Report_Type__c = 'OppHourlyFeeLineItemFees' AND ObjectID__c LIKE '{pv0}%'

5. Update or create a custom button on the Opportunity layout to create the quotes. I call my project’s button “Proposal”. Here’s the code for the button. Pull the query object id’s from the Conga Queries page.

1
2
3
4
5
6
7
8
9
10
https://www.appextremes.com/apps/Conga/Composer.aspx
?sessionId={!API.Session_ID}
&serverUrl={!API.Partner_Server_URL_80}
&id={!Opportunity.Id}
&OFN=Proposal+for+{!Account.Name}
&LG4=1
&LG1=Proposal+Sent
&LG2=7
&pv0={!Opportunity.Id}
&QueryId=a07i0000005OCcQ,a07i0000005OCdi,a07i0000005OkML,a07i0000005Ok80

Conga Queries

6. Create a Conga template using the queries. There’s lot of info on Conga’s website for creating templates; here’s the main report table we are concerned with.

Conga Template

One thought on “Salesforce Custom Hours Billed Quote with Conga Composer

  1. Randall

    An example URL if using multiple query filter parameters:

    1
    2
    3
    4
    5
    6
    https://composer.congamerge.com
    ?sessionId={!API.Session_ID}
    &serverUrl={!API.Partner_Server_URL_290}
    &id={!Opportunity.Id}
    &QueryId=a0bM000000BhnCL
    ?pv0={!Opportunity.Id}~pv1={!Opportunity.AccountId}
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *