1

Help needed while doing deployment. I am getting the following error message -

ERROR MESSAGE - Run Failures: TestTrg_UpdateResourceBillingInfo.myUnitTest System.LimitException: Too many SOQL queries: 101

I have loaded the Apex Trigger at the below link - https://www.dropbox.com/s/02sob7o6206hqph/Update%20Reseource%20Billing%20Info.txt?m=

Test Class is mentioned at below link - https://www.dropbox.com/sm/create/Code%20Practice/Test%20class.txt

Please let me know how to fix this issues as tomorrow is my go live date and this is troubling me horribly.

Trigger

/*
This trigger is used to auto-populate billing values of a Resource from Resource & Billing History table into Startsheet.
*/

trigger trg_UpdateResourceBillingInfo on StartSheet__c (After insert, After update) {

Public Boolean SalaryChanged = FALSE;
Public String CurrentSalary;
Set<ID> sSheetId = new Set<ID>();
Set<ID> resourceld = new Set<ID>();

List<Startsheet__c> lstSheet = new List<Startsheet__c>();
List<Billing_History__c> lstBillingHst = new List<Billing_History__c>();
List<Customer_Billing__c> lstCstBilling = new List<Customer_Billing__c>();
system.debug('******************************heerere : ');
for(Startsheet__c s:trigger.new)
    { 
     if(s.Resource__c!= NULL)
      {
        sSheetId.add(s.Id);
        resourceld.add(s.Resource__c);
      }

      //getting old value of Employee Annual Salary
      if(trigger.isAfter && trigger.isUpdate){
      Startsheet__c oldSalary = Trigger.oldMap.get(s.Id);
      if(s.Field1__c != oldSalary.Field1__c)     
          SalaryChanged = TRUE;         
      }      
    }


system.debug('******************************sSheetId : '+sSheetId);
system.debug('******************************resourceld : '+resourceld);
system.debug('******************************date__c : ');


lstSheet = [Select Id,Consultant_s_Type__c,Agency_Employee_Salary__c,Agency_Employee_Current_Salary__c,Hourly_Cost__c,Billing_Rate__c,
            Field1__c,Field2__c,Field3__c,Field4__c,GP_Rate_Percent__c,Current_Billing_Rate__c from Startsheet__c where Id in:sSheetId and Resource__c in:resourceld];




 lstBillingHst = [Select Id,Date__c,Resource__c,CreatedDate,Employee_Annual_Salary__c,Company_Unit__c from Billing_History__c where Resource__c in:resourceld 
               and date__c <=: date.Today() order by createdDate desc limit 1];



//lstBillingHst = [Select Id,Date__c,Resource__c,CreatedDate,Employee_Annual_Salary__c,Company_Unit__c from Billing_History__c where Resource__c in:resourceld];



lstCstBilling = [Select Agency_Rate_Hour__c,Agency_Rate_Month__c,Current_Hourly_Billing_Rate__c,Current_Monthly_Billing_Rate__c,Customer__c,
                 Customer_Type__c,Description__c,Employee_Annual_Salary__c,End_Date__c,GP_Rate__c,Hourly_Cost__c,Payment_Terms__c,PO_Number__c,
                 Project_Type__c,Resource__c,Sales_Person__c,Start_Date__c,Startsheet__c from Customer_Billing__c where Startsheet__c in:sSheetId];

system.debug('******************************lstSheet : '+lstSheet);
system.debug('******************************lstBillingHst : '+lstBillingHst);
system.debug('******************************lstCstBilling : '+lstCstBilling);

if(trigger.isAfter && trigger.isInsert)
{
for(Startsheet__c st:lstSheet)
  {        
    if(st.Consultant_s_Type__c=='Hired from Agency')
    {

       //Calculation of GP Rate%          
      if(st.Billing_Rate__c=='Hourly')
      {
         //st.Hourly_Cost__c = st.Field1__c;
        //st.GP_Rate_Percent__c = (((st.Field2__c - st.Hourly_Cost__c)/st.Field2__c)*100);
          st.GP_Rate_Percent__c = (st.Field2__c - st.Hourly_Cost__c)/st.Field2__c*100;

      }
      if(st.Billing_Rate__c=='Monthly')
      {
        //st.Hourly_Cost__c = st.Field1__c/160;
        //st.GP_Rate_Percent__c = ((((st.Field2__c/160)- st.Hourly_Cost__c)/(st.Field2__c/160))*100);
        //st.GP_Rate_Percent__c = (st.Field2__c/160 - st.Hourly_Cost__c)/(st.Field2__c/160)*100;
        //st.GP_Rate_Percent__c = (st.Field2__c - st.Field1__c)/160;
        st.GP_Rate_Percent__c = ((st.Field2__c - st.Field1__c)/st.Field2__c)*100;

      }
      update lstSheet;
     } 

    system.debug('***********inside**********lstSheet : '+lstSheet);
    system.debug('***********inside*************lstBillingHst : '+lstBillingHst);
      for(Billing_History__c lst:lstBillingHst)
      {
         if(st.Consultant_s_Type__c!='Hired from Agency')   //if Consultant is W2-Employee or W2-Special Employee
         {
          st.Field1__c = lst.Employee_Annual_Salary__c;
         }

        //Calculation of Hourly Cost  
        system.debug('***********inside y*************lstBillingHst : '+lstBillingHst);
          system.debug('***********inside y*************st : '+st);

         if(st.Consultant_s_Type__c=='W2 Employee' && lst.Company_Unit__c=='RSI')
          st.Hourly_Cost__c = (st.Field1__c*1.30)/1920;

         if(st.Consultant_s_Type__c=='W2 Employee')
          st.Hourly_Cost__c = (st.Field1__c*1.30)/1920;

         if(st.Consultant_s_Type__c=='W2-Special Employee' && lst.Company_Unit__c=='RSI')
          st.Hourly_Cost__c = (st.Field1__c*1.08)/1920;

         if(st.Consultant_s_Type__c=='W2-Special Employee')
          st.Hourly_Cost__c = (st.Field1__c*1.08)/1800;

        //Calculation of GP Rate%          
         if(st.Billing_Rate__c=='Hourly')
         {
           st.GP_Rate_Percent__c = (st.Field2__c - st.Hourly_Cost__c)/st.Field2__c*100;
         }
         if(st.Billing_Rate__c=='Monthly')
         {
           if(st.Consultant_s_Type__c=='W2 Employee')
           {
             st.GP_Rate_Percent__c = ((st.Field2__c - st.Field1__c/12)/st.Field2__c)*100;
           }
           else
           {
             st.GP_Rate_Percent__c = ((st.Field2__c - st.Field1__c)/st.Field2__c)*100;
           }

         }
         update lstSheet;                        
      }        
  }
}

system.debug('***************************** Before trigger call');
if(stopRecursiveCall.recursiveCall())   //calling apex class stopRecursiveCall to stop recursive call of trigger
 {    
        system.debug('*****t************************trigger'+trigger.new);
  if(trigger.isAfter && trigger.isUpdate)
   {
    system.debug('*****t************************lstSheet'+lstSheet);
    for(Startsheet__c st:lstSheet)
     {
        if(st.Consultant_s_Type__c=='Hired from Agency')
        {
          st.Hourly_Cost__c = st.Field1__c;
           //Calculation of GP Rate%          
          if(st.Billing_Rate__c=='Hourly')
            st.GP_Rate_Percent__c = (((st.Field2__c - st.Hourly_Cost__c)/st.Field2__c)*100);
          if(st.Billing_Rate__c=='Monthly')
          {
            //st.GP_Rate_Percent__c = ((((st.Field2__c/160)- st.Hourly_Cost__c)/(st.Field2__c/160))*100);
            //st.GP_Rate_Percent__c = (st.Field2__c - st.Field1__c)/160;
            st.GP_Rate_Percent__c = ((st.Field2__c - st.Field1__c)/st.Field2__c)*100;
          }
         update lstSheet;
        } 
        //if(st.Consultant_s_Type__c!='Hired from Agency')
        //{  
        //  for(Billing_History__c lst:lstBillingHst)
        //  {st.Field1__c = lst.Employee_Annual_Salary__c;}
        //}
      for(Billing_History__c lst:lstBillingHst)
       {
        system.debug('***************************** Inside After Update Loop');
 //      if(SalaryChanged == TRUE){

        /*if(st.Consultant_s_Type__c!='Hired from Agency')   //if Consultant is W2-Employee or W2-Special Employee
         {          
          st.Field1__c = lst.Employee_Annual_Salary__c;
          st.Agency_Employee_Salary__c = 'Employee Current Annual Salary';     
         }*/

        //Calculation of Hourly Cost  
        if(st.Consultant_s_Type__c=='W2 Employee' && lst.Company_Unit__c=='RSI')
        {
          st.Hourly_Cost__c = (st.Field1__c*1.30)/1920;
        }
        if(st.Consultant_s_Type__c=='W2 Employee')
        {
          st.Hourly_Cost__c = (st.Field1__c*1.30)/1920;
        }
        if(st.Consultant_s_Type__c=='W2-Special Employee' && lst.Company_Unit__c=='RSI')
        {
          st.Hourly_Cost__c = (st.Field1__c*1.08)/1920;
        }
        if(st.Consultant_s_Type__c=='W2-Special Employee')
        {
          st.Hourly_Cost__c = (st.Field1__c*1.08)/1800;
        }

        //Calculation of GP Rate%          
        if(st.Billing_Rate__c=='Hourly')
          st.GP_Rate_Percent__c = (((st.Field2__c - st.Hourly_Cost__c)/st.Field2__c)*100);
        /*if(st.Billing_Rate__c=='Monthly')
        {
         st.GP_Rate_Percent__c = ((st.Field2__c - st.Field1__c)/st.Field2__c)*100;
        }*/
        if(st.Billing_Rate__c=='Monthly')
         {
           if(st.Consultant_s_Type__c=='W2 Employee')
           {
             st.GP_Rate_Percent__c = ((st.Field2__c - st.Field1__c/12)/st.Field2__c)*100;
           }
           else
           {
             st.GP_Rate_Percent__c = ((st.Field2__c - st.Field1__c)/st.Field2__c)*100;
           }

         }      

       system.debug('*************************************GP Rate% : '+st.GP_Rate_Percent__c);
       system.debug('*************************************Hourly Cost : '+st.Hourly_Cost__c);

        update lstSheet;                  
   //    }
      }      
     }
   }
 }

}
6
  • 2
    Can't access the test file getting a 404 error. – Jenny B Jul 17 '14 at 17:40
  • +jjbennet can't see it – EricSSH Jul 17 '14 at 17:50
  • 1
    Please put your code directly into your question instead of linking to it. – Alex Tennant Jul 17 '14 at 17:53
  • 2
    At first glance, you are using update lstSheet in your for loops – EricSSH Jul 17 '14 at 17:53
  • 1
    Your queries are inside of your for loop. You will need to redesign your trigger to use collections to get them outside of the loops. – Eric Aug 15 '14 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.