+ Start a Discussion
JavagalJavagal 

Soql Error : too many Queries 101

Hi , I am Getting an error to the old code since we made some changes to the other classes. below is the test Stack Trace. Any help will be really appriciated.
-------------------------------------------------------------------
Error Message System.LimitException: Too many SOQL queries: 101
Class.leadTriggerHandler.assignOwner:line 86, column 1
Class.leadTriggerHandler.onBeforeInsert:line 36, column 1
Trigger.LeadsTrigger: line 4, column 1
public without sharing class leadTriggerHandler {
    
        
     public static void onBeforeInsert(list<Lead> newLeads){
     list<Lead> leadsToProcess = new list<Lead>(); 
        for(Lead l : newLeads){
            
            /* Check if owner is already populated and not currentUser and zipcode is not null, Do_Not_Reassign__c is false
               only then drive the lead through custom assignment rule. */   
            if((l.OwnerId == null || l.OwnerId == userinfo.getUserId()) && l.Zip_Postal_Code__c != null 
                && (l.Products__c != null || l.Facility_Type__c != null)
                && (l.Do_Not_Reassign__c == null || l.Do_Not_Reassign__c == false)){
                    
                leadsToProcess.add(l);
            }else if(l.Zip_Postal_Code__c == null || (l.Products__c == null && l.Facility_Type__c == null) ){
                //no ZipCode match (or) no product and no facility type -  assign the defaultleadOwner.
                l.OwnerId = SystemIdUtility.KCIUSUserId;
            }
        }
        if(!leadsToProcess.isEmpty()){
            assignOwner(leadsToProcess);
        }
     
     }
     
    
     public static void onBeforeUpdate(list<Lead> newLeads, map<Id,Lead> oldValuesMap){
     list<Lead> leadsToProcess = new list<Lead>(); 
        for(Lead l : newLeads){ 
            if(l.Zip_Postal_Code__c != null && 
               (l.Zip_Postal_Code__c!=oldValuesMap.get(l.Id).Zip_Postal_Code__c ||
                l.Facility_Type__c!=oldValuesMap.get(l.Id).Facility_Type__c)){
                    
                leadsToProcess.add(l);
            }
        }
        if(!leadsToProcess.isEmpty()){
            assignOwner(leadsToProcess);
        }
     
     }
     
    
     private static void assignOwner(list<Lead> leadList){
     
     try{
             
             set<String> leadZipCodeSet =  new set<String>();
             list<Lead> clonedLeadsToInsert =  new list<Lead>();
             
                 for(lead l : leadList){
                    if(string.valueOf(l.Zip_Postal_Code__c).length() > 5){
                        leadZipCodeSet.add(string.valueOf(l.Zip_Postal_Code__c).left(5));
                    }else{
                        leadZipCodeSet.add(string.valueOf(l.Zip_Postal_Code__c));
                    }
                 }
                 list<ZIP_Code__c> zipCodeList = [SELECT Name, Is_Active__c, KCI_Territory__c, 
                                                        KCI_Territory__r.Territory_Code__c, KCI_Territory__r.OwnerId 
                                                        FROM ZIP_Code__c 
                                                        where Name In :  leadZipCodeSet and Is_Active__c = 'Y'
                                                        ];
                 for(Lead l : leadList){
                    set<Id> leadOwnerIdSet =  new set<Id>();
                    if(l.Products__c != null){
                        //Product is multiselect picklist and facility is single picklist. For each product and facility, get the JCA from the custom setting
                        for(String product : String.ValueOf(l.Products__c).split(';')){
                            String JCA = '';
                            for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){
                                if(product.equalsIgnoreCase(String.ValueOf(las.Product__c))){
                                    if(l.Facility_Type__c != null && l.Facility_Type__c == String.ValueOf(las.Facility_Type__c)){
                                        JCA = String.ValueOf(las.JCA__c);
                                        system.debug('@@@ CS Product & FT -- ' + Product + ' -- ' + l.Facility_Type__c);
                                        break;
                                    }else if(l.Facility_Type__c == null && las.Facility_Type__c == null){
                                        JCA = String.ValueOf(las.JCA__c);
                                        system.debug('@@@ CS Product & FT --  ' + Product + ' -- ' + l.Facility_Type__c);
                                    }
                                }
                            }
                            
                            if(JCA != ''){
                                for(ZIP_Code__c zip : zipCodeList){
                                    if( zip.Name == string.valueOf(l.Zip_Postal_Code__c).left(5) && 
                                        zip.KCI_Territory__r.Territory_Code__c != null && zip.KCI_Territory__r.Territory_Code__c.startsWith(JCA)){
                                        
                                        leadOwnerIdSet.add(zip.KCI_Territory__r.OwnerId);
                                        break;
                                    }
                                }
                            }
                            system.debug('@@@ Leads Product & FT -- ' + Product + ' -- ' + l.Facility_Type__c);
                            system.debug('@@@ JCA -- ' + JCA);
                            system.debug('@@@ leadOwnerIdSet -- ' + leadOwnerIdSet);
                        }
                    } else if(l.Products__c == null && l.Facility_Type__c != null){
                        String JCA = '';
                        for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){
                            if(las.Product__c == null){
                                if(l.Facility_Type__c != null && l.Facility_Type__c == String.ValueOf(las.Facility_Type__c)){
                                    JCA = String.ValueOf(las.JCA__c);
                                    break;
                                }
                            }
                        }
                        if(JCA != ''){
                            for(ZIP_Code__c zip : zipCodeList){
                                if( zip.Name == string.valueOf(l.Zip_Postal_Code__c).left(5) && 
                                    zip.KCI_Territory__r.Territory_Code__c != null && zip.KCI_Territory__r.Territory_Code__c.startsWith(JCA)){
                                    
                                    leadOwnerIdSet.add(zip.KCI_Territory__r.OwnerId);
                                    break;
                                }
                            }
                        }
                    }
                    
                    if(!leadOwnerIdSet.isEmpty() && leadOwnerIdSet.size() == 1){
                        // only one owner idendified for the lead based on product, facility type and zipcode combination
                        l.OwnerId = new list<Id>(leadOwnerIdSet)[0];
                        system.debug('>>>@@@ in lead owner Assignment - Owner = '+ l.OwnerId );
                    }else if(!leadOwnerIdSet.isEmpty() && leadOwnerIdSet.size() > 1){
                        //multiple owners identified, assign the first one to the current lead and create clones to assign to other owners.
                        list<Id> leadOwnerIdList = new list<Id>(leadOwnerIdSet);
                        for(integer i = 1; i < leadOwnerIdList.size(); i++){
                            Lead clonedLead = l.clone(false, true);
                            clonedLead.OwnerId = leadOwnerIdList[i];
                            clonedLead.Do_Not_Reassign__c = true;
                            clonedLeadsToInsert.add(clonedLead);
                        }
                        l.OwnerId = leadOwnerIdList[0];
                        system.debug('>>>@@@ in multi lead owner Assignment - Owner = '+ l.OwnerId );
                    }else{
                        //no owner matching the criteria assign owner to KCI US User
                        l.OwnerId = SystemIdUtility.KCIUSUserId;
                    }   
                 
                 }
                 
                 if(!clonedLeadsToInsert.isEmpty()){
                    insert(clonedLeadsToInsert);
                 }
             
             
     
     }
     catch(Exception e){
     
     }
 
  }

}
----------------------------- 

Trigger 

trigger LeadsTrigger on Lead (before insert, before update) {
	
	if(trigger.isInsert && trigger.isBefore){
		leadTriggerHandler.onBeforeInsert(trigger.new);
	}
	if(trigger.isUpdate && trigger.isBefore){
		leadTriggerHandler.onBeforeUpdate(trigger.new, trigger.oldMap);
	}

}
---------------------------- 

/**********************************************************************************************************************
 * Class Name	: LeadTriggerHandlerTest
 * Description	: Testr Class for LeadTriggerHandler.
 **********************************************************************************************************************/
@isTest(SeeAllData=true)
private class LeadTriggerHandlerTest {
	
    public static Map<String,User> testUsersMap;

    static testMethod void leadOwnerAssignmentTest() {
        Lead testLead = new Lead();
        setupTestData();
        test.startTest();
	        // Test null or blank zip code - owner should be default lead owner KCI US User
	          testLead = createLead('ABThera','Acute','');
	          system.assertEquals(SystemIdUtility.KCIUSUserId, [select OwnerId from Lead where Id=:testLead.Id].OwnerId);
	          
        	// loop through all combinations as configured in Lead Assignment custom setting and create leads with the sample values
        	for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){
	        	testLead = createLead(String.ValueOf(las.Product__c),String.ValueOf(las.Facility_Type__c),'63000');
	        	if(Limits.getQueries() < Limits.getLimitQueries()-20){
	        		if(testUsersMap.get(String.ValueOf(las.JCA__c)) != null){	
	        			system.assertEquals(testUsersMap.get(String.ValueOf(las.JCA__c)).Id, 
	        						[select OwnerId from Lead where Id=:testLead.Id].OwnerId);
	        		}
	        	}
        	}
        	// update Lead and test Owner assignment.
        	testLead.Products__c = 'Ulta';
        	testLead.Facility_Type__c = 'Acute';
        	update testLead;
        	
        	// Test multiple product
        	testLead = createLead('Specialty Dressing;GRAFTJACKET','Acute','63000');
        	
        	// Test non matching zipcode - then Lead Owner assigned should be KCI US User
        	testLead = createLead('ABThera','Acute','99999');
        	system.assertEquals(SystemIdUtility.KCIUSUserId, [select OwnerId from Lead where Id=:testLead.Id].OwnerId);
        	
        test.stopTest();
    }
    
    private static Lead createLead(String product, String facilityType, String zipCode){
    
    	Lead newLead = new Lead();
    	newLead.FirstName = 'FirstName';
    	newLead.LastName = 'LastName';
    	newLead.Email = 'test@kci1.com';
    	newLead.Products__c = product;
    	newLead.Facility_Type__c = facilityType;
    	newLead.Zip_Postal_Code__c = zipCode;
    	
    	insert newLead;
    	
    	return newLead;
    
    }
    
    private static void setupTestData(){
		//get test users
		testUsersMap = new Map<String,User>();
		list<User> testTMVUsers=[Select Id,JCA__C, Employee_Id__c from User where IsActive=true and Employee_Id__c!=null and JCA__C='TMV' and Id !=:Userinfo.getUserId() limit 1];
		if(testTMVUsers!= null && testTMVUsers.size()==1){
			testUsersMap.put('TMV',testTMVUsers[0]);
		}		
		list<User> testTSVUsers=[Select Id,JCA__C, Employee_Id__c from User where IsActive=true and Employee_Id__c!=null and JCA__C='TSV' and Id !=:Userinfo.getUserId() limit 1];
		if(testTSVUsers!= null && testTSVUsers.size()==1){
			testUsersMap.put('TSV',testTSVUsers[0]);
		}
		list<User> testSSMUsers=[Select Id,JCA__C, Employee_Id__c from User where IsActive=true and Employee_Id__c!=null and JCA__C='SSM' and Id !=:Userinfo.getUserId() limit 1];
		if(testSSMUsers!= null && testSSMUsers.size()==1){
			testUsersMap.put('SSM',testSSMUsers[0]);
		}	
		
		//create Territory and Zip code for test user
		
		//create territory 
    	KCI_Territory__c testTerrTMV=new KCI_Territory__c(Name='terr-01', KCI_Legacy_Id__c='terr-001',Sales_Rep_Id__c=testUsersMap.get('TMV').employee_id__c, 
    	Territory_Code__c='TMV-test terr code',Territory_Start_Date__c=System.date.Today()-5, OwnerId=testUsersMap.get('TMV').Id);
    	KCI_Territory__c testTerrTSV=new KCI_Territory__c(Name='terr-02', KCI_Legacy_Id__c='terr-002',Sales_Rep_Id__c=testUsersMap.get('TSV').employee_id__c, 
    	Territory_Code__c='TSV-test terr code',Territory_Start_Date__c=System.date.Today()-5, OwnerId=testUsersMap.get('TSV').Id);
    	KCI_Territory__c testTerrSSM=new KCI_Territory__c(Name='terr-03', KCI_Legacy_Id__c='terr-003',Sales_Rep_Id__c=testUsersMap.get('SSM').employee_id__c, 
    	Territory_Code__c='SSM-test terr code',Territory_Start_Date__c=System.date.Today()-5, OwnerId=testUsersMap.get('SSM').Id);
    	insert new list<KCI_Territory__c>{testTerrTMV,testTerrTSV,testTerrSSM};
    	
		//create zip codes
    	ZIP_Code__c testZipTMV=new ZIP_Code__c(Name='63000', KCI_Legacy_Id__c='zip-001',KCI_Territory__c=testTerrTMV.Id,Zip_Start_Date__c=System.date.Today()-5);
    	ZIP_Code__c testZipTSV=new ZIP_Code__c(Name='63000', KCI_Legacy_Id__c='zip-002',KCI_Territory__c=testTerrTSV.Id,Zip_Start_Date__c=System.date.Today()-5);
    	ZIP_Code__c testZipSSM=new ZIP_Code__c(Name='63000', KCI_Legacy_Id__c='zip-003',KCI_Territory__c=testTerrSSM.Id,Zip_Start_Date__c=System.date.Today()-5);
    	insert new list<ZIP_Code__c>{testZipTMV,testZipTSV,testZipSSM};
    
    }
}


Class.leadTriggerHandler.assignOwner: line 86, column 1
Class.leadTriggerHandler.onBeforeInsert: line 36, column 1
Trigger.LeadsTrigger: line 4, column 1
Best Answer chosen by Javagal
SantoshChitalkarSantoshChitalkar
Oh my bad, please replace your class code with following :- 
 
public without sharing class leadTriggerHandler {
    
     // To control recurssive execution
     public static boolean shouldRunTrigger  = true;
        
     public static void onBeforeInsert(list<Lead> newLeads){
     list<Lead> leadsToProcess = new list<Lead>(); 
        for(Lead l : newLeads){
            
            /* Check if owner is already populated and not currentUser and zipcode is not null, Do_Not_Reassign__c is false
               only then drive the lead through custom assignment rule. */   
            if((l.OwnerId == null || l.OwnerId == userinfo.getUserId()) && l.Zip_Postal_Code__c != null 
                && (l.Products__c != null || l.Facility_Type__c != null)
                && (l.Do_Not_Reassign__c == null || l.Do_Not_Reassign__c == false)){
                    
                leadsToProcess.add(l);
            }else if(l.Zip_Postal_Code__c == null || (l.Products__c == null && l.Facility_Type__c == null) ){
                //no ZipCode match (or) no product and no facility type -  assign the defaultleadOwner.
                l.OwnerId = SystemIdUtility.KCIUSUserId;
            }
        }

        shouldRunTrigger = false;
        
        if(!leadsToProcess.isEmpty()){
            assignOwner(leadsToProcess);
        }
     
     }
     
    
     public static void onBeforeUpdate(list<Lead> newLeads, map<Id,Lead> oldValuesMap){
     list<Lead> leadsToProcess = new list<Lead>(); 
        for(Lead l : newLeads){ 
            if(l.Zip_Postal_Code__c != null && 
               (l.Zip_Postal_Code__c!=oldValuesMap.get(l.Id).Zip_Postal_Code__c ||
                l.Facility_Type__c!=oldValuesMap.get(l.Id).Facility_Type__c)){
                    
                leadsToProcess.add(l);
            }
        }
        if(!leadsToProcess.isEmpty()){
            assignOwner(leadsToProcess);
        }
     
     }
     
    
     private static void assignOwner(list<Lead> leadList){
     
     try{
             
             set<String> leadZipCodeSet =  new set<String>();
             list<Lead> clonedLeadsToInsert =  new list<Lead>();
             
                 for(lead l : leadList){
                    if(string.valueOf(l.Zip_Postal_Code__c).length() > 5){
                        leadZipCodeSet.add(string.valueOf(l.Zip_Postal_Code__c).left(5));
                    }else{
                        leadZipCodeSet.add(string.valueOf(l.Zip_Postal_Code__c));
                    }
                 }
                 list<ZIP_Code__c> zipCodeList = [SELECT Name, Is_Active__c, KCI_Territory__c, 
                                                        KCI_Territory__r.Territory_Code__c, KCI_Territory__r.OwnerId 
                                                        FROM ZIP_Code__c 
                                                        where Name In :  leadZipCodeSet and Is_Active__c = 'Y'
                                                        ];
                 for(Lead l : leadList){
                    set<Id> leadOwnerIdSet =  new set<Id>();
                    if(l.Products__c != null){
                        //Product is multiselect picklist and facility is single picklist. For each product and facility, get the JCA from the custom setting
                        for(String product : String.ValueOf(l.Products__c).split(';')){
                            String JCA = '';
                            for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){
                                if(product.equalsIgnoreCase(String.ValueOf(las.Product__c))){
                                    if(l.Facility_Type__c != null && l.Facility_Type__c == String.ValueOf(las.Facility_Type__c)){
                                        JCA = String.ValueOf(las.JCA__c);
                                        system.debug('@@@ CS Product & FT -- ' + Product + ' -- ' + l.Facility_Type__c);
                                        break;
                                    }else if(l.Facility_Type__c == null && las.Facility_Type__c == null){
                                        JCA = String.ValueOf(las.JCA__c);
                                        system.debug('@@@ CS Product & FT --  ' + Product + ' -- ' + l.Facility_Type__c);
                                    }
                                }
                            }
                            
                            if(JCA != ''){
                                for(ZIP_Code__c zip : zipCodeList){
                                    if( zip.Name == string.valueOf(l.Zip_Postal_Code__c).left(5) && 
                                        zip.KCI_Territory__r.Territory_Code__c != null && zip.KCI_Territory__r.Territory_Code__c.startsWith(JCA)){
                                        
                                        leadOwnerIdSet.add(zip.KCI_Territory__r.OwnerId);
                                        break;
                                    }
                                }
                            }
                            system.debug('@@@ Leads Product & FT -- ' + Product + ' -- ' + l.Facility_Type__c);
                            system.debug('@@@ JCA -- ' + JCA);
                            system.debug('@@@ leadOwnerIdSet -- ' + leadOwnerIdSet);
                        }
                    } else if(l.Products__c == null && l.Facility_Type__c != null){
                        String JCA = '';
                        for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){
                            if(las.Product__c == null){
                                if(l.Facility_Type__c != null && l.Facility_Type__c == String.ValueOf(las.Facility_Type__c)){
                                    JCA = String.ValueOf(las.JCA__c);
                                    break;
                                }
                            }
                        }
                        if(JCA != ''){
                            for(ZIP_Code__c zip : zipCodeList){
                                if( zip.Name == string.valueOf(l.Zip_Postal_Code__c).left(5) && 
                                    zip.KCI_Territory__r.Territory_Code__c != null && zip.KCI_Territory__r.Territory_Code__c.startsWith(JCA)){
                                    
                                    leadOwnerIdSet.add(zip.KCI_Territory__r.OwnerId);
                                    break;
                                }
                            }
                        }
                    }
                    
                    if(!leadOwnerIdSet.isEmpty() && leadOwnerIdSet.size() == 1){
                        // only one owner idendified for the lead based on product, facility type and zipcode combination
                        l.OwnerId = new list<Id>(leadOwnerIdSet)[0];
                        system.debug('>>>@@@ in lead owner Assignment - Owner = '+ l.OwnerId );
                    }else if(!leadOwnerIdSet.isEmpty() && leadOwnerIdSet.size() > 1){
                        //multiple owners identified, assign the first one to the current lead and create clones to assign to other owners.
                        list<Id> leadOwnerIdList = new list<Id>(leadOwnerIdSet);
                        for(integer i = 1; i < leadOwnerIdList.size(); i++){
                            Lead clonedLead = l.clone(false, true);
                            clonedLead.OwnerId = leadOwnerIdList[i];
                            clonedLead.Do_Not_Reassign__c = true;
                            clonedLeadsToInsert.add(clonedLead);
                        }
                        l.OwnerId = leadOwnerIdList[0];
                        system.debug('>>>@@@ in multi lead owner Assignment - Owner = '+ l.OwnerId );
                    }else{
                        //no owner matching the criteria assign owner to KCI US User
                        l.OwnerId = SystemIdUtility.KCIUSUserId;
                    }   
                 
                 }
                 
                 if(!clonedLeadsToInsert.isEmpty()){
                    insert(clonedLeadsToInsert);
                 }
             
             
     
     }
     catch(Exception e){
     
     }
 
  }

}

 

All Answers

SantoshChitalkarSantoshChitalkar
Hi Javagal,

Try the following code. Let me know if this solves your problem or not.
public without sharing class leadTriggerHandler {
    
     // To control recurssive execution
     public static boolean shouldRunTrigger  = true;
        
     public static void onBeforeInsert(list<Lead> newLeads){
     list<Lead> leadsToProcess = new list<Lead>(); 
        for(Lead l : newLeads){
            
            /* Check if owner is already populated and not currentUser and zipcode is not null, Do_Not_Reassign__c is false
               only then drive the lead through custom assignment rule. */   
            if((l.OwnerId == null || l.OwnerId == userinfo.getUserId()) && l.Zip_Postal_Code__c != null 
                && (l.Products__c != null || l.Facility_Type__c != null)
                && (l.Do_Not_Reassign__c == null || l.Do_Not_Reassign__c == false)){
                    
                leadsToProcess.add(l);
            }else if(l.Zip_Postal_Code__c == null || (l.Products__c == null && l.Facility_Type__c == null) ){
                //no ZipCode match (or) no product and no facility type -  assign the defaultleadOwner.
                l.OwnerId = SystemIdUtility.KCIUSUserId;
            }
        }
        if(!leadsToProcess.isEmpty()){
            assignOwner(leadsToProcess);
        }
     
     }
     
    
     public static void onBeforeUpdate(list<Lead> newLeads, map<Id,Lead> oldValuesMap){
     list<Lead> leadsToProcess = new list<Lead>(); 
        for(Lead l : newLeads){ 
            if(l.Zip_Postal_Code__c != null && 
               (l.Zip_Postal_Code__c!=oldValuesMap.get(l.Id).Zip_Postal_Code__c ||
                l.Facility_Type__c!=oldValuesMap.get(l.Id).Facility_Type__c)){
                    
                leadsToProcess.add(l);
            }
        }
        if(!leadsToProcess.isEmpty()){
            assignOwner(leadsToProcess);
        }
     
     }
     
    
     private static void assignOwner(list<Lead> leadList){
     
     try{
             
             set<String> leadZipCodeSet =  new set<String>();
             list<Lead> clonedLeadsToInsert =  new list<Lead>();
             
                 for(lead l : leadList){
                    if(string.valueOf(l.Zip_Postal_Code__c).length() > 5){
                        leadZipCodeSet.add(string.valueOf(l.Zip_Postal_Code__c).left(5));
                    }else{
                        leadZipCodeSet.add(string.valueOf(l.Zip_Postal_Code__c));
                    }
                 }
                 list<ZIP_Code__c> zipCodeList = [SELECT Name, Is_Active__c, KCI_Territory__c, 
                                                        KCI_Territory__r.Territory_Code__c, KCI_Territory__r.OwnerId 
                                                        FROM ZIP_Code__c 
                                                        where Name In :  leadZipCodeSet and Is_Active__c = 'Y'
                                                        ];
                 for(Lead l : leadList){
                    set<Id> leadOwnerIdSet =  new set<Id>();
                    if(l.Products__c != null){
                        //Product is multiselect picklist and facility is single picklist. For each product and facility, get the JCA from the custom setting
                        for(String product : String.ValueOf(l.Products__c).split(';')){
                            String JCA = '';
                            for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){
                                if(product.equalsIgnoreCase(String.ValueOf(las.Product__c))){
                                    if(l.Facility_Type__c != null && l.Facility_Type__c == String.ValueOf(las.Facility_Type__c)){
                                        JCA = String.ValueOf(las.JCA__c);
                                        system.debug('@@@ CS Product & FT -- ' + Product + ' -- ' + l.Facility_Type__c);
                                        break;
                                    }else if(l.Facility_Type__c == null && las.Facility_Type__c == null){
                                        JCA = String.ValueOf(las.JCA__c);
                                        system.debug('@@@ CS Product & FT --  ' + Product + ' -- ' + l.Facility_Type__c);
                                    }
                                }
                            }
                            
                            if(JCA != ''){
                                for(ZIP_Code__c zip : zipCodeList){
                                    if( zip.Name == string.valueOf(l.Zip_Postal_Code__c).left(5) && 
                                        zip.KCI_Territory__r.Territory_Code__c != null && zip.KCI_Territory__r.Territory_Code__c.startsWith(JCA)){
                                        
                                        leadOwnerIdSet.add(zip.KCI_Territory__r.OwnerId);
                                        break;
                                    }
                                }
                            }
                            system.debug('@@@ Leads Product & FT -- ' + Product + ' -- ' + l.Facility_Type__c);
                            system.debug('@@@ JCA -- ' + JCA);
                            system.debug('@@@ leadOwnerIdSet -- ' + leadOwnerIdSet);
                        }
                    } else if(l.Products__c == null && l.Facility_Type__c != null){
                        String JCA = '';
                        for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){
                            if(las.Product__c == null){
                                if(l.Facility_Type__c != null && l.Facility_Type__c == String.ValueOf(las.Facility_Type__c)){
                                    JCA = String.ValueOf(las.JCA__c);
                                    break;
                                }
                            }
                        }
                        if(JCA != ''){
                            for(ZIP_Code__c zip : zipCodeList){
                                if( zip.Name == string.valueOf(l.Zip_Postal_Code__c).left(5) && 
                                    zip.KCI_Territory__r.Territory_Code__c != null && zip.KCI_Territory__r.Territory_Code__c.startsWith(JCA)){
                                    
                                    leadOwnerIdSet.add(zip.KCI_Territory__r.OwnerId);
                                    break;
                                }
                            }
                        }
                    }
                    
                    if(!leadOwnerIdSet.isEmpty() && leadOwnerIdSet.size() == 1){
                        // only one owner idendified for the lead based on product, facility type and zipcode combination
                        l.OwnerId = new list<Id>(leadOwnerIdSet)[0];
                        system.debug('>>>@@@ in lead owner Assignment - Owner = '+ l.OwnerId );
                    }else if(!leadOwnerIdSet.isEmpty() && leadOwnerIdSet.size() > 1){
                        //multiple owners identified, assign the first one to the current lead and create clones to assign to other owners.
                        list<Id> leadOwnerIdList = new list<Id>(leadOwnerIdSet);
                        for(integer i = 1; i < leadOwnerIdList.size(); i++){
                            Lead clonedLead = l.clone(false, true);
                            clonedLead.OwnerId = leadOwnerIdList[i];
                            clonedLead.Do_Not_Reassign__c = true;
                            clonedLeadsToInsert.add(clonedLead);
                        }
                        l.OwnerId = leadOwnerIdList[0];
                        system.debug('>>>@@@ in multi lead owner Assignment - Owner = '+ l.OwnerId );
                    }else{
                        //no owner matching the criteria assign owner to KCI US User
                        l.OwnerId = SystemIdUtility.KCIUSUserId;
                    }   
                 
                 }
                 
                 if(!clonedLeadsToInsert.isEmpty()){
                    insert(clonedLeadsToInsert);
                 }
             
             
     
     }
     catch(Exception e){
     
     }
 
  }

}
----------------------------- 

Trigger 

trigger LeadsTrigger on Lead (before insert, before update) {
	
         if( ! leadTriggerHandler.shouldRunTrigger)
                return;

	if(trigger.isInsert && trigger.isBefore){
		leadTriggerHandler.onBeforeInsert(trigger.new);
	}
	if(trigger.isUpdate && trigger.isBefore){
		leadTriggerHandler.onBeforeUpdate(trigger.new, trigger.oldMap);
	}

}
---------------------------- 

/**********************************************************************************************************************
 * Class Name	: LeadTriggerHandlerTest
 * Description	: Testr Class for LeadTriggerHandler.
 **********************************************************************************************************************/
@isTest(SeeAllData=true)
private class LeadTriggerHandlerTest {
	
    public static Map<String,User> testUsersMap;

    static testMethod void leadOwnerAssignmentTest() {
        Lead testLead = new Lead();
        setupTestData();
        test.startTest();
	        // Test null or blank zip code - owner should be default lead owner KCI US User
	          testLead = createLead('ABThera','Acute','');
	          system.assertEquals(SystemIdUtility.KCIUSUserId, [select OwnerId from Lead where Id=:testLead.Id].OwnerId);
	          
        	// loop through all combinations as configured in Lead Assignment custom setting and create leads with the sample values
        	for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){
	        	testLead = createLead(String.ValueOf(las.Product__c),String.ValueOf(las.Facility_Type__c),'63000');
	        	if(Limits.getQueries() < Limits.getLimitQueries()-20){
	        		if(testUsersMap.get(String.ValueOf(las.JCA__c)) != null){	
	        			system.assertEquals(testUsersMap.get(String.ValueOf(las.JCA__c)).Id, 
	        						[select OwnerId from Lead where Id=:testLead.Id].OwnerId);
	        		}
	        	}
        	}
        	// update Lead and test Owner assignment.
        	testLead.Products__c = 'Ulta';
        	testLead.Facility_Type__c = 'Acute';
        	update testLead;
        	
        	// Test multiple product
        	testLead = createLead('Specialty Dressing;GRAFTJACKET','Acute','63000');
        	
        	// Test non matching zipcode - then Lead Owner assigned should be KCI US User
        	testLead = createLead('ABThera','Acute','99999');
        	system.assertEquals(SystemIdUtility.KCIUSUserId, [select OwnerId from Lead where Id=:testLead.Id].OwnerId);
        	
        test.stopTest();
    }
    
    private static Lead createLead(String product, String facilityType, String zipCode){
    
    	Lead newLead = new Lead();
    	newLead.FirstName = 'FirstName';
    	newLead.LastName = 'LastName';
    	newLead.Email = 'test@kci1.com';
    	newLead.Products__c = product;
    	newLead.Facility_Type__c = facilityType;
    	newLead.Zip_Postal_Code__c = zipCode;
    	
    	insert newLead;
    	
    	return newLead;
    
    }
    
    private static void setupTestData(){
		//get test users
		testUsersMap = new Map<String,User>();
		list<User> testTMVUsers=[Select Id,JCA__C, Employee_Id__c from User where IsActive=true and Employee_Id__c!=null and JCA__C='TMV' and Id !=:Userinfo.getUserId() limit 1];
		if(testTMVUsers!= null && testTMVUsers.size()==1){
			testUsersMap.put('TMV',testTMVUsers[0]);
		}		
		list<User> testTSVUsers=[Select Id,JCA__C, Employee_Id__c from User where IsActive=true and Employee_Id__c!=null and JCA__C='TSV' and Id !=:Userinfo.getUserId() limit 1];
		if(testTSVUsers!= null && testTSVUsers.size()==1){
			testUsersMap.put('TSV',testTSVUsers[0]);
		}
		list<User> testSSMUsers=[Select Id,JCA__C, Employee_Id__c from User where IsActive=true and Employee_Id__c!=null and JCA__C='SSM' and Id !=:Userinfo.getUserId() limit 1];
		if(testSSMUsers!= null && testSSMUsers.size()==1){
			testUsersMap.put('SSM',testSSMUsers[0]);
		}	
		
		//create Territory and Zip code for test user
		
		//create territory 
    	KCI_Territory__c testTerrTMV=new KCI_Territory__c(Name='terr-01', KCI_Legacy_Id__c='terr-001',Sales_Rep_Id__c=testUsersMap.get('TMV').employee_id__c, 
    	Territory_Code__c='TMV-test terr code',Territory_Start_Date__c=System.date.Today()-5, OwnerId=testUsersMap.get('TMV').Id);
    	KCI_Territory__c testTerrTSV=new KCI_Territory__c(Name='terr-02', KCI_Legacy_Id__c='terr-002',Sales_Rep_Id__c=testUsersMap.get('TSV').employee_id__c, 
    	Territory_Code__c='TSV-test terr code',Territory_Start_Date__c=System.date.Today()-5, OwnerId=testUsersMap.get('TSV').Id);
    	KCI_Territory__c testTerrSSM=new KCI_Territory__c(Name='terr-03', KCI_Legacy_Id__c='terr-003',Sales_Rep_Id__c=testUsersMap.get('SSM').employee_id__c, 
    	Territory_Code__c='SSM-test terr code',Territory_Start_Date__c=System.date.Today()-5, OwnerId=testUsersMap.get('SSM').Id);
    	insert new list<KCI_Territory__c>{testTerrTMV,testTerrTSV,testTerrSSM};
    	
		//create zip codes
    	ZIP_Code__c testZipTMV=new ZIP_Code__c(Name='63000', KCI_Legacy_Id__c='zip-001',KCI_Territory__c=testTerrTMV.Id,Zip_Start_Date__c=System.date.Today()-5);
    	ZIP_Code__c testZipTSV=new ZIP_Code__c(Name='63000', KCI_Legacy_Id__c='zip-002',KCI_Territory__c=testTerrTSV.Id,Zip_Start_Date__c=System.date.Today()-5);
    	ZIP_Code__c testZipSSM=new ZIP_Code__c(Name='63000', KCI_Legacy_Id__c='zip-003',KCI_Territory__c=testTerrSSM.Id,Zip_Start_Date__c=System.date.Today()-5);
    	insert new list<ZIP_Code__c>{testZipTMV,testZipTSV,testZipSSM};
    
    }
}

Regards,
Santosh Chitalkar
JavagalJavagal
Hi Santosh , Thanks for your reply. I am still getting the Same error after the changes as well.
SantoshChitalkarSantoshChitalkar
Oh my bad, please replace your class code with following :- 
 
public without sharing class leadTriggerHandler {
    
     // To control recurssive execution
     public static boolean shouldRunTrigger  = true;
        
     public static void onBeforeInsert(list<Lead> newLeads){
     list<Lead> leadsToProcess = new list<Lead>(); 
        for(Lead l : newLeads){
            
            /* Check if owner is already populated and not currentUser and zipcode is not null, Do_Not_Reassign__c is false
               only then drive the lead through custom assignment rule. */   
            if((l.OwnerId == null || l.OwnerId == userinfo.getUserId()) && l.Zip_Postal_Code__c != null 
                && (l.Products__c != null || l.Facility_Type__c != null)
                && (l.Do_Not_Reassign__c == null || l.Do_Not_Reassign__c == false)){
                    
                leadsToProcess.add(l);
            }else if(l.Zip_Postal_Code__c == null || (l.Products__c == null && l.Facility_Type__c == null) ){
                //no ZipCode match (or) no product and no facility type -  assign the defaultleadOwner.
                l.OwnerId = SystemIdUtility.KCIUSUserId;
            }
        }

        shouldRunTrigger = false;
        
        if(!leadsToProcess.isEmpty()){
            assignOwner(leadsToProcess);
        }
     
     }
     
    
     public static void onBeforeUpdate(list<Lead> newLeads, map<Id,Lead> oldValuesMap){
     list<Lead> leadsToProcess = new list<Lead>(); 
        for(Lead l : newLeads){ 
            if(l.Zip_Postal_Code__c != null && 
               (l.Zip_Postal_Code__c!=oldValuesMap.get(l.Id).Zip_Postal_Code__c ||
                l.Facility_Type__c!=oldValuesMap.get(l.Id).Facility_Type__c)){
                    
                leadsToProcess.add(l);
            }
        }
        if(!leadsToProcess.isEmpty()){
            assignOwner(leadsToProcess);
        }
     
     }
     
    
     private static void assignOwner(list<Lead> leadList){
     
     try{
             
             set<String> leadZipCodeSet =  new set<String>();
             list<Lead> clonedLeadsToInsert =  new list<Lead>();
             
                 for(lead l : leadList){
                    if(string.valueOf(l.Zip_Postal_Code__c).length() > 5){
                        leadZipCodeSet.add(string.valueOf(l.Zip_Postal_Code__c).left(5));
                    }else{
                        leadZipCodeSet.add(string.valueOf(l.Zip_Postal_Code__c));
                    }
                 }
                 list<ZIP_Code__c> zipCodeList = [SELECT Name, Is_Active__c, KCI_Territory__c, 
                                                        KCI_Territory__r.Territory_Code__c, KCI_Territory__r.OwnerId 
                                                        FROM ZIP_Code__c 
                                                        where Name In :  leadZipCodeSet and Is_Active__c = 'Y'
                                                        ];
                 for(Lead l : leadList){
                    set<Id> leadOwnerIdSet =  new set<Id>();
                    if(l.Products__c != null){
                        //Product is multiselect picklist and facility is single picklist. For each product and facility, get the JCA from the custom setting
                        for(String product : String.ValueOf(l.Products__c).split(';')){
                            String JCA = '';
                            for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){
                                if(product.equalsIgnoreCase(String.ValueOf(las.Product__c))){
                                    if(l.Facility_Type__c != null && l.Facility_Type__c == String.ValueOf(las.Facility_Type__c)){
                                        JCA = String.ValueOf(las.JCA__c);
                                        system.debug('@@@ CS Product & FT -- ' + Product + ' -- ' + l.Facility_Type__c);
                                        break;
                                    }else if(l.Facility_Type__c == null && las.Facility_Type__c == null){
                                        JCA = String.ValueOf(las.JCA__c);
                                        system.debug('@@@ CS Product & FT --  ' + Product + ' -- ' + l.Facility_Type__c);
                                    }
                                }
                            }
                            
                            if(JCA != ''){
                                for(ZIP_Code__c zip : zipCodeList){
                                    if( zip.Name == string.valueOf(l.Zip_Postal_Code__c).left(5) && 
                                        zip.KCI_Territory__r.Territory_Code__c != null && zip.KCI_Territory__r.Territory_Code__c.startsWith(JCA)){
                                        
                                        leadOwnerIdSet.add(zip.KCI_Territory__r.OwnerId);
                                        break;
                                    }
                                }
                            }
                            system.debug('@@@ Leads Product & FT -- ' + Product + ' -- ' + l.Facility_Type__c);
                            system.debug('@@@ JCA -- ' + JCA);
                            system.debug('@@@ leadOwnerIdSet -- ' + leadOwnerIdSet);
                        }
                    } else if(l.Products__c == null && l.Facility_Type__c != null){
                        String JCA = '';
                        for(Lead_Assignment_Settings__c las : Lead_Assignment_Settings__c.getAll().values()){
                            if(las.Product__c == null){
                                if(l.Facility_Type__c != null && l.Facility_Type__c == String.ValueOf(las.Facility_Type__c)){
                                    JCA = String.ValueOf(las.JCA__c);
                                    break;
                                }
                            }
                        }
                        if(JCA != ''){
                            for(ZIP_Code__c zip : zipCodeList){
                                if( zip.Name == string.valueOf(l.Zip_Postal_Code__c).left(5) && 
                                    zip.KCI_Territory__r.Territory_Code__c != null && zip.KCI_Territory__r.Territory_Code__c.startsWith(JCA)){
                                    
                                    leadOwnerIdSet.add(zip.KCI_Territory__r.OwnerId);
                                    break;
                                }
                            }
                        }
                    }
                    
                    if(!leadOwnerIdSet.isEmpty() && leadOwnerIdSet.size() == 1){
                        // only one owner idendified for the lead based on product, facility type and zipcode combination
                        l.OwnerId = new list<Id>(leadOwnerIdSet)[0];
                        system.debug('>>>@@@ in lead owner Assignment - Owner = '+ l.OwnerId );
                    }else if(!leadOwnerIdSet.isEmpty() && leadOwnerIdSet.size() > 1){
                        //multiple owners identified, assign the first one to the current lead and create clones to assign to other owners.
                        list<Id> leadOwnerIdList = new list<Id>(leadOwnerIdSet);
                        for(integer i = 1; i < leadOwnerIdList.size(); i++){
                            Lead clonedLead = l.clone(false, true);
                            clonedLead.OwnerId = leadOwnerIdList[i];
                            clonedLead.Do_Not_Reassign__c = true;
                            clonedLeadsToInsert.add(clonedLead);
                        }
                        l.OwnerId = leadOwnerIdList[0];
                        system.debug('>>>@@@ in multi lead owner Assignment - Owner = '+ l.OwnerId );
                    }else{
                        //no owner matching the criteria assign owner to KCI US User
                        l.OwnerId = SystemIdUtility.KCIUSUserId;
                    }   
                 
                 }
                 
                 if(!clonedLeadsToInsert.isEmpty()){
                    insert(clonedLeadsToInsert);
                 }
             
             
     
     }
     catch(Exception e){
     
     }
 
  }

}

 
This was selected as the best answer
Ashish_Sharma_DEVSFDCAshish_Sharma_DEVSFDC
Hi Javagal,

I think ,your below code is getting fired from your class which would be calling your trigger again and again.
Check your debug logs if this the case.
if(!clonedLeadsToInsert.isEmpty()){
     insert(clonedLeadsToInsert);
}

Solution:
Create a static member to avoid recursion and use it in your trigger as below.
public class sample{
       public static boolean hasTriggerFired = false;
}


Trigger 

trigger LeadsTrigger on Lead (before insert, before update) {
	if(hasTriggerFired == false){
			if(trigger.isInsert && trigger.isBefore){
			leadTriggerHandler.onBeforeInsert(trigger.new);
		}
		if(trigger.isUpdate && trigger.isBefore){
			leadTriggerHandler.onBeforeUpdate(trigger.new, trigger.oldMap);
		}	
	}
	hasTriggerFired = true;

}

Let us know if it helps you.
JavagalJavagal
Thank you Santosh and Ashish, it worked like a charm. but i want to ask you how would you guys find out it a recusive trigger issue by looking immediately ? i am bit curious.

Also on test class its only passing if i login as default lead owner User. its not working with any other user. i am getting System.assertion failed in all cases. it hav not happend earlier . what cause this test class failed again.

Thank you if these were basic Questions my Apology !!

Javagal.
Ashish_Sharma_DEVSFDCAshish_Sharma_DEVSFDC
Hi Javagal,

If you are getting SOQL 101 exception, generally it is because of recursive trigger.
So always check debug logs and look how many times your trigger is getting executed.