+ Start a Discussion
GMASJGMASJ 

System.LimitException: Too many SOQL queries: 101 in a perfect working trigger

Hi, 

 Below is the trigger which is working fine in sandbox when i deploy it is giving me below error from test class please suggest me how to fix this issue

ApprovalUtils_Test_New
System.LimitException: Too many SOQL queries: 101 
Stack Trace: Class.ApprovalUtils.GetSubscripItem: line 273, column 1 Trigger.GenQuoteApproval: line 67, column 1
 
trigger GenQuoteApproval on QuoteLineItem (After Insert, After Update,After Delete) {

  Decimal LineMaxDiscount;
  Decimal LineMaxACV;
  Decimal SubLineMaxDiscount;
  Decimal SubLineMaxACV;
  Decimal SerLineMaxDiscount;
  Decimal SerLineMaxACV;
  Decimal TraLineMaxDiscount;
  Decimal TraLineMaxACV;
  Map<ID, Quote> ParentQuote = new Map<ID, Quote>();
  String SALREPID;
  String MRGID;
  String SALID;
  String CFOID;
  String SERID;
  String TRAID;
  String Level;
  Integer GSublevel1Count;
  Integer GSublevel2Count;
  Integer GSublevel3Count;
  Integer GSublevel4Count;
  Integer GSerlevel1Count;
  Integer GSerlevel2Count;
  Integer GSerlevel3Count;
  Decimal SubscriptionTerm;
  Integer GTralevel1Count;
  Integer GTralevel2Count;
  Integer GTralevel3Count;
 

 
Try 
{
if ( Trigger.isAfter)
  {
 List<Id> listIds = new List<Id>();
 //Set<Id> listIds = new Set<Id>();

 List<Quote> QuotetList = new List<Quote>();

 /* Get Quote ID */
 for (QuoteLineItem childquoteline :  Trigger.new)
 {
        listIds.add(childquoteline.QuoteId);
    }
 
   
 ParentQuote = new Map<Id, Quote>([SELECT id,Level_1__c,Level_2__c,Level_3__c,Level_4__c,Level_5_Service__c,Level_6_Training__c FROM Quote WHERE ID IN :listIds]);
 
  /* Get service list of all quote id */
   list<QuoteLineItem> serqtlne = ApprovalUtils.GetServiceItem(listIds);
   
  //  [select id  from  QuoteLineItem
   //   where quoteid in :listIds and
   //   product2.Productcode  like 'CBSVC%'];
   
  list<id> serviceid = new list<id>();
  for(QuoteLineItem getserviceid : serqtlne )
   {
      serviceid.add(getserviceid.id);

   }


  /* Get subscription list of all quote id */
    list<QuoteLineItem> subqtlne = ApprovalUtils.GetSubscripItem(listIds);
    
   // [select id  from  QuoteLineItem
   //  where quoteid in :listIds and
   //  Subscription_Terms__c <> 0];
      
    
                                     
  list<id> subscriptionid = new list<id>();
  for(QuoteLineItem getsubscriptionid :  subqtlne )
   {
      subscriptionid.add(getsubscriptionid.id);

   }

 /* Subscription Discount and ACV */
    List<AggregateResult> MaxSubscription = [select max(Discount_Percent__c) SubQuoteLineMaxDiscount,sum(f_ACV__c) SubQuoteLineMaxACV
                                                   from  QuoteLineItem
                                                   where quoteid in :listIds and Subscription_Terms__c <> 0 and
                                                         ID not in :serviceid
                                                 ];

  for (AggregateResult SubQuoteMaxDiscount : MaxSubscription)
  {
  SubLineMaxDiscount = (Decimal)SubQuoteMaxDiscount.get('SubQuoteLineMaxDiscount');
  SubLineMaxACV = (Decimal)SubQuoteMaxDiscount.get('SubQuoteLineMaxACV');
  }

   system.debug('Subscription Line Discount  :' + SubLineMaxDiscount);
   system.debug('Subscription Line ACV  :' + SubLineMaxACV);

   /* Service Discount and ACV */
  List<AggregateResult> MaxService = [select max(Discount_Percent__c) SerQuoteLineMaxDiscount,SUM(f_ACV__c) SerQuoteLineMaxACV
                                      from  QuoteLineItem
                                      where quoteid in :listIds and product2.Productcode  like 'CBSVC%' and
                                            id not in :subscriptionid];

  for (AggregateResult SerQuoteMaxDiscount : MaxService)
  {
  SerLineMaxDiscount = (Decimal)SerQuoteMaxDiscount.get('SerQuoteLineMaxDiscount');
  SerLineMaxACV = (Decimal)SerQuoteMaxDiscount.get('SerQuoteLineMaxACV');
  }

   system.debug('Service Line Discount  :' + SerLineMaxDiscount);
   system.debug('Service Line ACV  :' + SerLineMaxACV);
   
   
   /* Training Discount and ACV */
  List<AggregateResult> MaxTraining = [select max(Discount_Percent__c) TraQuoteLineMaxDiscount,SUM(f_ACV__c) TraQuoteLineMaxACV
                                      from  QuoteLineItem
                                      where quoteid in :listIds and product2.Productcode  like 'T%' and
                                            id not in :subscriptionid and id not in :subscriptionid];

  for (AggregateResult TraQuoteMaxDiscount : MaxTraining)
  {
  TraLineMaxDiscount = (Decimal)TraQuoteMaxDiscount.get('TraQuoteLineMaxDiscount');
  TraLineMaxACV = (Decimal)TraQuoteMaxDiscount.get('TraQuoteLineMaxACV');
  }

   system.debug('Training Line Discount  :' + TraLineMaxDiscount);
   system.debug('Training Line ACV  :' + TraLineMaxACV);
   

  Opportunity Opp = [select ownerid
                    from opportunity
                    where
                    id in (select OpportunityId from quote where id in :listIds) ];

  system.debug(listIds);
  
  SubscriptionTerm = ApprovalUtils.GetSubscriptionTerm(listIds);
  

  system.debug('Get New Subscription Term' + SubscriptionTerm);
  
  /* Get Manager ID */
  User Usr = [select managerid from user where id = :opp.ownerid];

 /* Subscription Query to get level 1..4 values */
   if ( SubscriptionTerm != null &&
        SubLineMaxACV != null &&
        SubLineMaxDiscount != null)
   {
       GSublevel1Count = ApprovalUtils.SubLevel1(SubscriptionTerm , SubLineMaxACV , SubLineMaxDiscount);

       system.debug('Subscription Level1 :' + GSublevel1Count);

       GSublevel2Count = ApprovalUtils.SubLevel2(SubscriptionTerm , SubLineMaxACV , SubLineMaxDiscount);

       system.debug('Subscription Level2 :' + GSublevel2Count);

       GSublevel3Count = ApprovalUtils.SubLevel3(SubscriptionTerm , SubLineMaxACV , SubLineMaxDiscount);

       system.debug('Subscription Level3 :' + GSublevel3Count);

       GSublevel4Count = ApprovalUtils.SubLevel4(SubscriptionTerm , SubLineMaxACV , SubLineMaxDiscount);

       system.debug('Subscription Level4 :' + GSublevel4Count);

        
   }    
   
      /* Service Query to get level 1..4 values */
   if (SerLineMaxACV != null && SerLineMaxDiscount != null)
    {
       GSerlevel1Count = ApprovalUtils.SerLevel1(SerLineMaxACV,SerLineMaxDiscount);

       system.debug('Service Level1 :' + GSerlevel1Count);

        GSerlevel2Count = ApprovalUtils.SerLevel2(SerLineMaxACV,SerLineMaxDiscount);

       system.debug('Service Level2 :' + GSerlevel2Count);

        GSerlevel3Count = ApprovalUtils.SerLevel3(SerLineMaxACV,SerLineMaxDiscount);

       system.debug('Service Level3 :' + GSerlevel3Count);
    } 
    
        /* Training Query to get level 1..4 values */
   if (TraLineMaxACV != null && TraLineMaxDiscount != null)
    {
        GTralevel1Count = ApprovalUtils.TraLevel1(TraLineMaxACV,TraLineMaxDiscount);
        system.debug('Training Level1 :' + GTralevel1Count);
        
        GTralevel2Count = ApprovalUtils.TraLevel2(TraLineMaxACV,TraLineMaxDiscount);
        system.debug('Training Level2 :' + GTralevel2Count);
        
        GTralevel3Count = ApprovalUtils.TraLevel3(TraLineMaxACV,TraLineMaxDiscount);
        system.debug('Training Level3 :' + GTralevel3Count);
    }
    
     If( GSublevel1Count >= 1  || GSerlevel1Count >= 1  || GTralevel1Count >= 1)
     {
      SALREPID = Opp.OwnerId;
      }   
     
     
    If (GSublevel2Count >= 1 || GSerlevel2Count >= 1  || GTralevel2Count >= 1)
    {
      SALREPID = NULL;
      MRGID = Usr.managerid;
    }
 
    /* Future here you may have to change for amount > 1000000 if its going only to SVP */
   If ( GSublevel3Count >= 1 || GSerlevel3Count >= 1 ||  GTralevel3Count >= 1)
      {
      SALREPID = NULL;
      MRGID = Usr.managerid;
      SALID = '00580000007jaoA';
      }
   
    /* Assign this to Pete */ 
    If ( GTralevel3Count >= 1)
    {
     TRAID = '00580000006GbpI';
     }
    else
    {
     TRAID = NULL;
     }  
     
     /* Asssing to asif */
    If ( GSerlevel3Count >= 1)
    {
     SERID = '0053400000839zg';
     }
    else
    {
     SERID = NULL;
     } 
      
    If ( GSublevel4Count >= 1 )
    {
    SALREPID = NULL;
    MRGID = Usr.managerid;
    SALID = '00580000007jaoA';
    CFOID = '00534000008oOCr';
    Level = '4sub';
    }  
  
    //system.debug('Which Level :' + Level);
    system.debug('Sales Rep :' + SALREPID);
    system.debug('Manager :' + MRGID);
    system.debug('Sales Ops :' + SALID);
    system.debug('CEO CFO :'  + CFOID);
    system.debug('Service:' + SERID);
    system.debug('Training:' + TRAID);
 
  
for (QuoteLineItem gqtl : Trigger.new)
 {
   Quote MyParentQuote = ParentQuote.get(gqtl.QuoteId);

    MyParentQuote.Level_1__c = SALREPID;
    MyParentQuote.Level_2__c=MRGID;
    MyParentQuote.Level_3__c=SALID;
    MyParentQuote.Level_4__c=CFOID;
    MyParentQuote.Level_5_Service__c = SERID;
    MyParentQuote.Level_6_Training__c = TRAID;
 
  }

  update ParentQuote.values();
  

 }
  
}

catch(Exception e) {
    System.debug('The following exception has occurred: ' + e.getMessage());    
}
  
}

Below is the helper class 
public with sharing class ApprovalUtils {

  /* Subscription Discount Level 1 */
  public static Integer SubLevel1(Decimal Subscription, Decimal ACV, Decimal Discount) {
    
   Integer L1Count;  
    
   List<AggregateResult> Level1 = [select count(id) Level1Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Subscription' and 
              Subscription_Term__c = :Subscription and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              Sales_Rep_Lower__c <= :Discount and Sales_Rep_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel1 : Level1)
     {
       L1Count = ((Integer)arLevel1.get('Level1Count'));
       
     }         
                                     
    return L1Count;
  }
  
   /* Subscription Discount Level 2 */
   public static Integer SubLevel2(Decimal Subscription, Decimal ACV, Decimal Discount) {
    
   Integer L2Count;  
   
   List<AggregateResult> Level2 = [select count(id) Level2Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Subscription' and 
              Subscription_Term__c = :Subscription and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              Direct_Manager_Lower__c <= :Discount and Direct_Manager_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel2 : Level2)
     {
      L2Count = ((Integer)arLevel2.get('Level2Count'));  
     }        
                                      
    return L2Count;
  }
  
   /* Subscription Discount Level 3 */
   public static Integer SubLevel3(Decimal Subscription, Decimal ACV, Decimal Discount) {
    
   Integer L3Count;  
   
   List<AggregateResult> Level3 = [select count(id) Level3Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Subscription' and 
              Subscription_Term__c = :Subscription and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              SVP_Lower__c <= :Discount and SVP_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel3 : Level3)
     {
      L3Count = ((Integer)arLevel3.get('Level3Count'));
     }        
                                      
    return L3Count;
  }
  
  /* Subscription Discount Level 4 */
  public static Integer SubLevel4(Decimal Subscription, Decimal ACV, Decimal Discount) {
    
   Integer L4Count;  

   List<AggregateResult> Level4 = [select count(id) Level4Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Subscription' and 
              Subscription_Term__c = :Subscription and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              CEO_CFO__c <= :Discount and CEO_CFO_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel4 : Level4)
     {
      L4Count = ((Integer)arLevel4.get('Level4Count'));
     }        
                                      
    return L4Count;
  }
  
  /* Service Discount Level 1 */
  public static Integer SerLevel1(Decimal ACV,Decimal Discount) {
    
   Integer L1Count;  
   
   List<AggregateResult> Level1 = [select count(id) Level1Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Services' and 
              Subscription_Term__c = 1 and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              Sales_Rep_Lower__c <= :Discount and Sales_Rep_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel1 : Level1)
     {
      L1Count = ((Integer)arLevel1.get('Level1Count'));
     }        
                                      
    return L1Count;
  }
  
  /* Service Discount Level 2 */
    public static Integer SerLevel2(Decimal ACV,Decimal Discount) {
    
   Integer L2Count;  
   
   List<AggregateResult> Level2 = [select count(id) Level2Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Services' and 
              Subscription_Term__c = 1 and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              Direct_Manager_Lower__c <= :Discount and Direct_Manager_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel2 : Level2)
     {
      L2Count = ((Integer)arLevel2.get('Level2Count'));
     }        
                                      
    return L2Count;
  }


  /* Service Discount Level 3 */
    public static Integer SerLevel3(Decimal ACV,Decimal Discount) {
    
   Integer L3Count;  
   
   List<AggregateResult> Level3 = [select count(id) Level3Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Services' and 
              Subscription_Term__c = 1 and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              SVP_Lower__c <= :Discount and SVP_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel3 : Level3)
     {
      L3Count = ((Integer)arLevel3.get('Level3Count'));
     }        
                                      
    return L3Count;
  }
 
 
   /* Training Discount Level 1 */
  public static Integer TraLevel1(Decimal ACV,Decimal Discount) {
    
   Integer L1Count;  
   
   List<AggregateResult> Level1 = [select count(id) Level1Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Training' and 
              Subscription_Term__c = 1 and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              Sales_Rep_Lower__c <= :Discount and Sales_Rep_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel1 : Level1)
     {
      L1Count = ((Integer)arLevel1.get('Level1Count'));
     }        
                                      
    return L1Count;
  }
 
 
   /* Training Discount Level 2 */
    public static Integer TraLevel2(Decimal ACV,Decimal Discount) {
    
   Integer L2Count;  
   
   List<AggregateResult> Level2 = [select count(id) Level2Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Training' and 
              Subscription_Term__c = 1 and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              Direct_Manager_Lower__c <= :Discount and Direct_Manager_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel2 : Level2)
     {
      L2Count = ((Integer)arLevel2.get('Level2Count'));
     }        
                                      
    return L2Count;
  }
  
  
   /* Training Discount Level 3 */
   public static Integer TraLevel3(Decimal ACV,Decimal Discount) {
    
   Integer L3Count;  
   
   List<AggregateResult> Level3 = [select count(id) Level3Count
              from Discount_Schedule_Matrix__c
              where
              Type__c = 'Training' and 
              Subscription_Term__c = 1 and
              ACV_Lower__c <= :ACV  and ACV_Upper__c >= :ACV and
              SVP_Lower__c <= :Discount and SVP_Upper__c >= :Discount];
              
              
    for (AggregateResult arLevel3 : Level3)
     {
      L3Count = ((Integer)arLevel3.get('Level3Count'));
     }        
                                      
    return L3Count;
  }
  
  
    /* Service Discount Level 3 */
    public static Decimal GetSubscriptionTerm(list<Id> Quoteid) {
     
     Decimal SubscriptionTerm;
     
    /* Get Subscription Term */ 
    List<AggregateResult> MaxSubscrioptionYear = [select max(f_Approval_Subscription_Years__c) QSubscriptionYear 
                                                  from QuoteLineItem where quoteid in :Quoteid]; 
  
    
    for (AggregateResult GetSubscriptionYear : MaxSubscrioptionYear)
    { 
     SubscriptionTerm = (Decimal)GetSubscriptionYear.get('QSubscriptionYear');
      }
 
       return SubscriptionTerm;
    }
    
    
    public static List<QuoteLineItem> GetServiceItem(list<Id> listIds){
    
     list<QuoteLineItem> serqtlne = new list<QuoteLineItem>();
     Try
     {
     /* Get service list of all quote id */
      serqtlne = [select id  from  QuoteLineItem
                                     where quoteid in :listIds and
                                           product2.Productcode  like 'CBSVC%'];
      }
      catch(Exception ex)
     {
      System.debug(ex);

      }                                       
     
     return serqtlne;
     
     }
    
     public static List<QuoteLineItem> GetSubscripItem(list<Id> listIds){
      list<QuoteLineItem> subqtlne = new list<QuoteLineItem>();
     try
     {
     /* Get service list of all quote id */
      subqtlne = [select id  from  QuoteLineItem
                                     where quoteid in :listIds and
                                           Subscription_Terms__c <> 0];
                                           
                                             
      }
     catch(Exception ex)
     {
      System.debug(ex);
      system.debug('============='+ex.getMessage());
      system.debug('============='+ex.getLineNumber());
      system.debug('============='+ex.getStackTraceString());
      }                                       
     
     return subqtlne;
     
     }
       
}





Below is the Test class
@IsTest(seeAllData=true)
private class ApprovalUtils_Test_New{
    @isTest(SeeAllData=true) // required for using the standard pricebook, unfortunately
    private static void ApprovalUtilsTest() {
    
     test.StartTest();
     
                 ApprovalUtils.SubLevel1(1,0,0);
                 ApprovalUtils.SubLevel2(1,0,10);
                 ApprovalUtils.SubLevel3(1,0,11);
                 ApprovalUtils.SubLevel4(1,0,41);
               
                 ApprovalUtils.SerLevel1(1,1);
                 ApprovalUtils.SerLevel2(1,10);
                 ApprovalUtils.SerLevel3(1,41);
                 
                 ApprovalUtils.TraLevel1(1,1);
                 ApprovalUtils.TraLevel2(1,10);
                 ApprovalUtils.TraLevel3(1,41);
                 
                 
        Account a = new Account(Name = 'Test Account');
        insert a;

        Opportunity o = new Opportunity(Name = 'Test Opp', StageName = 'Test Stage', CloseDate = Date.today(), AccountId = a.Id);
        insert o;

        Pricebook2 pb = [select Id from Pricebook2 where IsStandard = true limit 1];

        Product2 p = new Product2(Name = 'CBSVC', isActive = true);
        insert p;

        PricebookEntry pbe = new PricebookEntry(Pricebook2Id = pb.Id, Product2Id = p.Id, UnitPrice = 1, isActive = true);
        insert pbe;

        Quote q = new Quote(Name = 'Test Quote', OpportunityId = o.Id, PriceBook2Id = pb.Id);
        insert q;

        QuoteLineItem qli = new QuoteLineItem(
            QuoteId = q.Id, 
            PriceBookEntryId = pbe.Id, 
            Quantity = 1, 
            UnitPrice = 500,
            Discount_Percent__c = 0,
            Subscription_Terms__c = 1,
            Number_of_Nodes__c = 10
            );
        insert qli;
        
        /* Training */
        Product2 pt = new Product2(Name = 'T', isActive = true);
        insert pt;

        PricebookEntry pbet = new PricebookEntry(Pricebook2Id = pb.Id, Product2Id = pt.Id, UnitPrice = 1, isActive = true);
        insert pbet;

        Quote qt = new Quote(Name = 'Test Quote', OpportunityId = o.Id, PriceBook2Id = pb.Id);
        insert qt;

        QuoteLineItem qlit = new QuoteLineItem(
            QuoteId = q.Id, 
            PriceBookEntryId = pbet.Id, 
            Quantity = 1, 
            UnitPrice = 500,
            Discount_Percent__c = 10,
            Subscription_Terms__c = 1,
            Number_of_Nodes__c = 10
            );
        insert qlit;
        
       test.StopTest(); 
                     
    }
     
    }


 
surya kanadhipatlasurya kanadhipatla
Sudhir,
Previously, we couldn’t create price book entries in an Apex test by default unless the test had access to organization data via the @isTest(SeeAllData=true)

But from Summer ’14 Release , its not required. Please see below link and pdate te Test class accordinly.

https://releasenotes.docs.salesforce.com/en-us/summer14/release-notes/rn_apex_price_books_in_tests.htm

Note: i could see your test class updated for not using SeeAllData=true per Summer ’14 Release, still not understood why did not yo remove SeeAllData=true from test class? 

The reason for failure is SeeAllData=true, can you tell me the reason why SeeAllData=true is still required in test class?