function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Gaurav AgnihotriGaurav Agnihotri 

Number of SOQL queries: 101 out of 100

Gurus, 
I am running into a war with SOQL queries. We are required to calculate Price and Discount from custom tables and it needs to iterate through all the Quote Line Items.  If the line items are more than 15, I am getting SOQL error.
Can someone help me to refine my apex classes.

here is my main class:
public class QuoteRefresh1 {
   public List<QuoteLineItem> items = new List<QuoteLineItem>();
   public string QuoteRefresh1(Id sQuoteId){
       Quote NewQuote;
       NewQuote=[SELECT Currency_Code_of_Account__c,Pelco_Account_Name__c,Customer_Number__c, Discount FROM Quote where Id=:sQuoteId limit 1];
       //String sQuoteAccountId=[SELECT Pelco_Account_Name__c FROM Quote where Id=:sQuoteId LIMIT 10].Pelco_Account_Name__c;
       string sQuoteAccountId=NewQuote.Pelco_Account_Name__c;
       system.debug('sQuoteAccountId='+sQuoteAccountId); 
       String CustomerNumber=NewQuote.Customer_Number__c;
       String sCurrency=NewQuote.Currency_Code_of_Account__c;
       double AccountDiscount=NewQuote.Discount;
       Account QAccount;
       QAccount=[SELECT Pricing_Scheme_Location__c,Currency_Code__c,Currency_Number__c,Ship_To_Territory__c from Account WHERE Id=:sQuoteAccountId];
       double shipToTerritory=QAccount.Ship_To_Territory__c;
       system.debug('Ship to Territory='+shipToTerritory);
        //getting pricing scheme location
        string priceSchemeLocation=QAccount.Pricing_Scheme_Location__c;
        system.debug('Pricing Scheme Location='+priceSchemeLocation);
        //getting Currency Code 
        string currencyCode=QAccount.Currency_Code__c;
        system.debug('Currency Code='+currencyCode);
         //getting Currency Number
        string currencyNumber=QAccount.Currency_Number__c;
       system.debug('Currency Number='+currencyNumber);
       //String CustomerNumber=sCustomerNumber;
       system.debug('CustomerNumber='+CustomerNumber); 
       for(QuoteLineItem pi:[SELECT Discount_Code__c,CreatedById,CreatedDate,Description,Id,IsDeleted,LastModifiedById,LastModifiedDate,LineNumber,ListPrice,PricebookEntryId,Product2Id,Product2.Item__c,Quantity,QuoteId,SortOrder,Subtotal,SystemModstamp,TotalPrice,UnitPrice,Currency__c,Dealer_Price__c,Regular_Price__c,discount FROM QuoteLineItem  where QuoteId =:sQuoteId ])
       {           
           string sProduct2Id= pi.Product2Id;
           String ItemNumber= pi.Product2.Item__c ;
           system.debug('ItemNumber='+ItemNumber);
            //calculating prices, discounts and currency
           
           CalculatePelcoDiscount2 NewDiscount= new CalculatePelcoDiscount2();
           double PelcoDiscount=NewDiscount.CalculatePelcoDiscount2(sQuoteAccountId,ItemNumber,CustomerNumber,pi.Discount_Code__c,AccountDiscount);
           CalculatePelcoPrice2 newPrice=new CalculatePelcoPrice2();
           double pelcoPrice=newPrice.CalculatePelcoPrice2(ItemNumber,CustomerNumber,shipToTerritory, priceSchemeLocation,currencyCode,currencyNumber);
           //string pelcoCurrency=newPrice.CalculatePelcoCurrency(CustomerNumber);
           //pi.Currency__c=pelcoCurrency;
           pi.Currency__c=sCurrency;
           system.debug('pelcoCurrency='+sCurrency);
           pi.Dealer_Price__c=pelcoPrice;
           system.debug('pelcoPrice='+pelcoPrice);
           pi.Discount=PelcoDiscount;
           system.debug('PelcoDiscount='+PelcoDiscount);
           pi.Incremental_Discount__c=0;
           pi.Discounted_Price__c=pelcoPrice*(1-(PelcoDiscount/100));
           //pi.CCO_Standard_Cost__c=[Select CCO_Standard_Cost__c FROM Product2 WHERE Item__c =:ItemNumber LIMIT 1 ].CCO_Standard_Cost__c;
           //pi.Reevaluate__c=FALSE;
           //pi.Discounted_Price__c=100;
           //adding records to the list
           items.add(pi);
       }
       if(items.size() > 0 )
        {
            update items;
        }
           return ('Success');  
   }
  }//end class

This function is calling Two classes:
public class CalculatePelcoPrice2 {
	Public double pelcoPrice;
    Public string pelcoCurrency;
    public double CalculatePelcoPrice2( String itemNumber, string customerNumber,double shipToTerritory,string priceSchemeLocation,string currencyCode, string currencyNumber){
        integer pelcoPriceCountPSL=0;
        integer pelcoPriceCountTerritory=0;
        integer priceBookCount=0;
        //making sure that Account Exists
        Integer accountCount=[SELECT count() FROM Account WHERE AccountNumber =: customerNumber LIMIT 10];
         system.debug('Customer#='+customerNumber);
         system.debug('Account Count='+accountCount);
        if (accountCount >0){
            //if Account exist creating a list to store the values returned by SOQL
            //List<Account> AccountList=[SELECT Pricing_Scheme_Location__c,Currency_Code__c,Currency_Number__c,Ship_To_Territory__c  FROM Account WHERE AccountNumber =: customerNumber LIMIT 1];
           /* Account Accountlist =new Account();
            AccountList=[SELECT Pricing_Scheme_Location__c,Currency_Code__c,Currency_Number__c,Ship_To_Territory__c  FROM Account WHERE AccountNumber =: customerNumber LIMIT 1];
            //initializing Pricing Scheme location
            //casting it to integer
            double shipToTerritory=AccountList.Ship_To_Territory__c;
             system.debug('Ship to Territory='+shipToTerritory);
            //getting pricing scheme location
            string priceSchemeLocation=AccountList.Pricing_Scheme_Location__c;
             system.debug('Pricing Scheme Location='+priceSchemeLocation);
            //getting Currency Code 
            string currencyCode=AccountList.Currency_Code__c;
            system.debug('Currency Code='+currencyCode);
            //getting Currency Number
            string currencyNumber=AccountList.Currency_Number__c;
            system.debug('Currency Number='+currencyNumber);
           */
            if (priceSchemeLocation!=null){
                          if (currencyCode != 'USD'){
                                system.debug('Price Scheme Location is not null and currency code is not USD');
                                pelcoPriceCountPSL=[SELECT count() FROM Pelco_Price_Book__c WHERE Item__c=:itemNumber AND Currency_Number__c=:currencyNumber AND Currency_Code__c=:currencyCode AND Price_Scheme_Location__c =: priceSchemeLocation LIMIT 10];
                              	system.debug('PelcoPriceCountPSL='+pelcoPriceCountPSL);  
                              	if (pelcoPriceCountPSL>0){
                                   pelcoPrice=[SELECT Dealer_Price__c FROM Pelco_Price_Book__c WHERE Item__c=:itemNumber AND Currency_Number__c=:currencyNumber AND Currency_Code__c=:currencyCode AND Price_Scheme_Location__c =: priceSchemeLocation LIMIT 1].Dealer_Price__c; 
                                    system.debug('pelcoPrice='+pelcoPrice);
                                }
                           }
                          if (currencyCode == 'USD'){
                              system.debug('Price Scheme Location is not null and currency code is USD');
                               pelcoPriceCountPSL=[SELECT count() FROM Pelco_Price_Book__c WHERE Item__c=:itemNumber AND Currency_Code__c=:currencyCode AND Price_Scheme_Location__c =: priceSchemeLocation LIMIT 10];
                              system.debug('PelcoPriceCountPSL='+pelcoPriceCountPSL);
                              if (pelcoPriceCountPSL>0){
                                 pelcoPrice=[SELECT Dealer_Price__c FROM Pelco_Price_Book__c WHERE Item__c=:itemNumber AND Currency_Code__c=:currencyCode AND Price_Scheme_Location__c =: priceSchemeLocation LIMIT 1].Dealer_Price__c; 
                                 system.debug('pelcoPrice='+pelcoPrice);
                              }
                          }                             
                       }
            if (priceSchemeLocation==null){
                        if (shipToTerritory >0){
                                if (currencyCode != 'USD'){
                                    system.debug('Price Scheme Location is  null and currency code is not in USD');
                                     pelcoPriceCountTerritory=[SELECT count() FROM Pelco_Price_Book__c WHERE Item__c=:itemNumber AND Currency_Number__c=:currencyNumber AND Currency_Code__c=:currencyCode AND Territory__c =: shipToTerritory LIMIT 10];
                                    //pelcoPriceCountTerritory=[SELECT count() FROM Pelco_Price_Book__c WHERE  Currency_Number__c=:currencyNumber  ];
                                    system.debug('Item#='+itemNumber);
                                    system.debug('Currency#='+currencyNumber);
                                    system.debug('Currency Code#='+currencyCode);
                                    system.debug('ShipToTerritory#='+shipToTerritory);
                                    system.debug('pelcoPriceCountTerritory='+pelcoPriceCountTerritory);
                                    if (pelcoPriceCountTerritory>0){
                                      pelcoPrice=[SELECT Dealer_Price__c FROM Pelco_Price_Book__c WHERE Item__c=:itemNumber AND Currency_Number__c=:currencyNumber AND Currency_Code__c=:currencyCode AND Territory__c =: shipToTerritory LIMIT 1].Dealer_Price__c;  
                                    	system.debug('pelcoPrice='+pelcoPrice);
                                    }
                                    
                               }
                              if (currencyCode == 'USD'){
                                  system.debug('Price Scheme Location is null and currency code is in USD');
                                   pelcoPriceCountTerritory=[SELECT count() FROM Pelco_Price_Book__c WHERE Item__c=:itemNumber AND Currency_Code__c=:currencyCode AND Territory__c =: shipToTerritory LIMIT 10];
                                  	system.debug('pelcoPriceCountTerritory='+pelcoPriceCountTerritory);  
                                  	if (pelcoPriceCountTerritory>0){
                                        pelcoPrice=[SELECT Dealer_Price__c FROM Pelco_Price_Book__c WHERE Item__c=:itemNumber AND Currency_Code__c=:currencyCode AND Territory__c =: shipToTerritory LIMIT 1].Dealer_Price__c;
                                    	system.debug('pelcoPrice='+pelcoPrice);
                                    }
                                 }   
                          	}//if shipToTerritory is not null
                      	 } //if priceSchemeLocation is null
            if (pelcoPriceCountTerritory==0 && pelcoPriceCountPSL==0){
                    system.debug('Price was not found in REPITM');
                    priceBookCount=[SELECT count() FROM PricebookEntry WHERE Name =: itemNumber LIMIT 10];
                    system.debug('price book count='+priceBookCount);
                	if (priceBookCount>0){
                    		pelcoPrice=[SELECT UnitPrice FROM PricebookEntry WHERE Name =: itemNumber LIMIT 1].UnitPrice;
                             system.debug('pelcoPrice='+pelcoPrice);
            			}
               }//end if price is not found in REPITM
                   
    	}//end if Account exists
         return(pelcoPrice);
    }//end function
}//end class

and 
public class CalculatePelcoDiscount2 {
    public Double sDiscount=0;
    public double CalculatePelcoDiscount2(Id sQuoteAccountId,string itemNumber, string customerNumber, string sDiscountCode,double AccountDiscount ){
       // String sDiscountCode=null;
        //making sure that the item number exist in product2 table
        //Integer DiscountCodeCount=[SELECT count() FROM Product2 WHERE Item__c =: itemNumber LIMIT 10];
        system.debug('discount code='+sDiscountCode);
        system.debug('AccountDiscount='+AccountDiscount);
         //if (DiscountCodeCount>0){
                  // if item number exists, then geting the discount code from the product2 table
                 // sDiscountCode=[SELECT Discount_Code__c FROM Product2 WHERE Item__c =: itemNumber LIMIT 1].Discount_Code__c;
                 // system.debug('Discount code= '+sDiscountCode); 
               // } 

          if (sDiscountCode == 'A'){
              system.debug('Discount = 0 because Discount code is A'); 
              sDiscount=0;
              
              // return(sDiscount);
              }
          if (sDiscountCode != 'A' && sDiscountCode!=null){
                  //checking if discount exist in special discount table
                  Integer DiscountCount=[SELECT count() FROM Special_Discount__c WHERE Name =:customerNumber AND Item__c =:itemNumber LIMIT 10];
                  system.debug('Discount Count= '+DiscountCount); 
                  if (DiscountCount>0){
                        //look up int he special discount table for specific item
                        sDiscount=[SELECT Discount__c FROM Special_Discount__c WHERE Name =:customerNumber AND Item__c =:itemNumber LIMIT 1].Discount__c;
                         sDiscount=sDiscount*100;
                        system.debug('Discount code is not eqaul to A and is ='+sDiscountCode); 
                        system.debug('Discount when discount is not equal to A='+sDiscount);
                        
                         //return(sDiscount);
                        }
                    //if there is no special discount for the customer then get the discount value from the customer data
                    if (DiscountCount==0 ){
                        //sDiscount=AccountDiscount;
                        sDiscount=[SELECT Discount__c FROM Account WHERE Id =:sQuoteAccountId].Discount__c;
                        system.debug('Discount code not found in special discount table'); 
                       // checking if the account exist with customer number
                       //Integer accountCount=[SELECT count() FROM Account WHERE AccountNumber =: customerNumber LIMIT 10];
                       //if (accountCount >0){
                           //string sAccountId=[SELECT Id FROM Account WHERE AccountNumber =: customerNumber LIMIT 1].Id;
                           //sDiscount=[SELECT Discount__c FROM Account WHERE Id =:sAccountId].Discount__c;
                           
                           //sDiscount=sDiscount*100;
                           //return(sDiscount);
                           //}//end accountCount
                      }//end DiscountCount
				}// if discount is not equal to A
              system.debug('Final Discount='+sDiscount); 
             return(sDiscount);
    }//end of CalculatePelcoDiscount
}

I know, I am not proud of this... 
I would appreciate any suggestion
Shane RossShane Ross
This:
priceBookCount=[SELECT count() FROM PricebookEntry WHERE Name =: itemNumber LIMIT 10];
if (priceBookCount>0){
    pelcoPrice=[SELECT UnitPrice FROM PricebookEntry WHERE Name =: itemNumber LIMIT 1].UnitPrice;
}

can be shortened into:
priceBookCount=[SELECT UnitPrice FROM PricebookEntry WHERE Name =: itemNumber LIMIT 10];
if(PriceBookCount.size() < 0){
    pelcoPrice=priceBookCount[0].UnitPrice;
}

There are a number of instances in your code you could remove a query like this.
James LoghryJames Loghry
At approximately line 27 of your QuoteRefresh1 class, you start a for loop over quote line items.  Inside that loop, you're calling the calculatepelcodiscount2 method, which also performs SOQL queries. Thus, if you have say 95 Quote Line Items (or enough line items to bump you over the governor limit), an exception is thrown.  Please look into utilizing maps or refactoring your code to handle the SOQL queries more efficiently.  Also, you should probably check out the following link: https://developer.salesforce.com/blogs/developer-relations/2015/01/apex-best-practices-15-apex-commandments.html 
Amit Chaudhary 8Amit Chaudhary 8
System.LimitException: Too many SOQL queries: 101

The "System.LimitException: Too many SOQL queries: 101" error appears when you've hit the Execution Governors limit, which means you can run a total 100 SOQL queries in a context.  All the triggers fired will be counted in a single context or call. To fix the issue, you'll need to change your code in such a way that SOQL fired is less than 100. If you need to change the context then you can use @future annotation which will run the code asynchronously.

Resolution
Here are some best practices that will stop the error messages and/or help you avoid hitting the Governors Limit: 
1. Since Apex runs on a Multitenant structure, Apex runtime engine strictly enforces limits to ensure code doesn't monopolize shared resources. Learn about the Governors Limit.
2. Avoid SOQL queries that are inside FOR loop. 
3. Check out the Salesforce Developer Blog where you can find Best Practices for Triggers.
4. Review best practices for Trigger and Bulk requests on our Force.com Apex Code Developer's Guide. 
5. Be sure you're following the key coding principals for Apex Code in our Developer's Guide.

http://www.sfdc99.com/2013/11/23/the-three-most-common-governor-limits-and-why-youre-getting-them/

Please let us know if this will help you.

Thanks
Amit Chaudhary