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 required employee hours billed under dynamic categories. The quote needed each employee’s hours listed under the associated category.

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).

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.

3. Create an APEX trigger that will fill the report table each time an hours item is added to our opportunity. I keep my logic APEX 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.

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 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 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 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;
        
        }
        
      }         
        
    }
     
  }
  
}

Leave a Reply

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