You need to sign in to do that
Don't have an account?
GMASJ
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
Below is the helper class
Below is the Test class
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(); } }
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?