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
SFDC Admin & AnalystSFDC Admin & Analyst 

CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, SGSFA_OPPTY_UK_UPDATE_CNTRCT: Test Methods Fail in Production

Hi

Test Methods in our org failed suddenly with the following error:

 

Failure Message: "System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, SGSFA_OPPTY_UK_UPDATE_CNTRCT: execution of AfterUpdate caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact ...

 

It seems the test methods failed because of a trigger on oppportunity after update

 Here is the trigger:

 trigger SGSFA_OPPTY_UK_UPDATE_CNTRCT on Opportunity (after update) {
    //Trigger to uncheck Related Opportunity checkbox from contract object
     
     //Select UK Record Type
     RecordType rType =    [Select Id From RecordType 
                            where sObjectType='Opportunity'
                            and DeveloperName = 'UK_All_opportunity_rec_type' 
                            and isActive=true
                            limit 1];
                            
     List<Id> lstOppID = new List<Id>();
     
     for(Opportunity o : Trigger.New){
        if(o.recordTypeID == rType.Id){
            lstOppID.add(o.Id);
        }
     }
     
     List<Id> lstCloseDateChanged =  new List<id>();
     
     for(Opportunity o : Trigger.New){
           if(o.CloseDate != Trigger.oldMap.get(o.id).CloseDate)
              lstCloseDateChanged.add(o.id);      
     }
     
     //find the list of contracts to update
     List<Contract> lstCntrct = new List<Contract> ();
     lstCntrct = [select id, Related_Opportunity__c from contract c
                                 where c.id in (select o.Contract_ID__c From OpportunityLineItem o 
                                                where o.OpportunityID in :lstOppID)
                                 and c.id not in (select o1.Contract_ID__c From OpportunityLineItem o1
                                                  where o1.Opportunity.IsClosed = False)
                                ];
     //set related opportunity field to false and update the contract list                          
     if(!lstCntrct.isEmpty()) {
        for(Integer i = 0; i < lstCntrct.size(); i++){
            lstCntrct[i].Related_Opportunity__c = false;
        }
        
        update lstCntrct;   
     } 
     
     //update all opportunity products if the close date changed. This will trigged the WF rules
     if(!lstCloseDateChanged.isEmpty()){
         List<OpportunityLineItem> lstLine = [select id From OpportunityLineItem o 
                                                 where o.OpportunityID in :lstCloseDateChanged];
         if(!lstLine.isEmpty())
             update lstLine;
     }
}

 

I don't understand what is making test method fail. Any help will be greatly appreciated. Thanks

 

Below is the test method which is failing at Insert oppli

 

 static testmethod void test_batchoppty()
    {
          
        List<Opportunity> oppna = new List<Opportunity>();        
        List<Contact> cont = new List<Contact>();        
        List<OpportunityContactRole> controle = new List<OpportunityContactRole>();    
        
        System.runAs(SalesRep){
            Opportunity oppna1 = new Opportunity();
            Opportunity oppna2 = new Opportunity();
            Opportunity oppna3 = new Opportunity();
            Account acc = new Account();
            acc.Name='Test Acc';
            insert acc;
            RecordType rec = [Select id from RecordType where SObjectType='Opportunity' AND Name like 'NA%' limit 1];
            oppna1.recordtypeId = rec.Id;
            oppna1.AccountId=acc.Id;
            oppna1.Name='Test';
            oppna1.CloseDate=system.today();
            oppna1.StageName='1-In Legal';
            oppna1.Oracle_Quote_ID__c = 'Test001';
            oppna.add(oppna1);
            insert oppna;
            
            Contact ct1 = new Contact(FirstName='TestContact1', LastName = 'Test',AccountId=acc.Id);
        cont.add(ct1);
        //Contact ct2 = new Contact(FirstName='TestContact2', LastName = 'Test',AccountId=acc.Id);
        //cont.add(ct2);
        //Contact ct3 = new Contact(FirstName='TestContact3', LastName = 'Test',AccountId=acc.Id);
       // cont.add(ct3);
            insert cont;
            
            OpportunityContactRole cr1 = new OpportunityContactRole(ContactId = ct1.id , OpportunityId= oppna1.Id);
            controle.add(cr1);
            //OpportunityContactRole cr2 = new OpportunityContactRole(ContactId = ct2.id , OpportunityId= oppna2.Id);
            // controle.add(cr2);
            //OpportunityContactRole cr3 = new OpportunityContactRole(ContactId = ct3.id , OpportunityId= oppna3.Id);
           //controle.add(cr3);
           
           insert controle;
            
            Product2 prd1 = new Product2(Name='TestProduct1',Family='Recovery Services'); 
            Product2 prd2 = new Product2(Name='TestProduct2',Family='Managed Services');
            Product2 prd3 = new Product2(Name='TestProduct3',Family='Recovery Services');
            Product2 prd4 = new Product2(Name='TestProduct4',Family='Consulting Services');
            Product2 prd5 = new Product2(Name='TestProduct5',Family='Software Services');
            Product2[] prd = new Product2[]{prd1,prd2,prd3,prd4,prd5};
            insert prd;
            
            Pricebook2 pb = [select Id from Pricebook2 where IsStandard=true]; 
            
            PricebookEntry pbe1 = new PricebookEntry(Product2Id=prd[0].Id, Pricebook2Id=pb.Id,UnitPrice=12,IsActive=true);
            PricebookEntry pbe2 = new PricebookEntry(Product2Id=prd[1].Id, Pricebook2Id=pb.Id,UnitPrice=14,IsActive=true);
            PricebookEntry pbe3 = new PricebookEntry(Product2Id=prd[2].Id, Pricebook2Id=pb.Id,UnitPrice=13,IsActive=true);
            PricebookEntry pbe4 = new PricebookEntry(Product2Id=prd[3].Id, Pricebook2Id=pb.Id,UnitPrice=12,IsActive=true);
            PricebookEntry pbe5 = new PricebookEntry(Product2Id=prd[4].Id, Pricebook2Id=pb.Id,UnitPrice=14,IsActive=true);
            PricebookEntry[] pbe = new PricebookEntry[]{pbe1,pbe2,pbe3,pbe4,pbe5};
            insert pbe; 
            
            OpportunityLineItem oli1 = new OpportunityLineItem(OpportunityId=oppna[0].Id,PricebookEntryId=pbe[0].Id,Quantity=2,TotalPrice=20,Incremental_GCR__c=12,Gross_GCR__c=22,Gross_MRR__c=21,Stretch_MRR__c=11);
            OpportunityLineItem oli2 = new OpportunityLineItem(OpportunityId=oppna[0].Id,PricebookEntryId=pbe[1].Id,Quantity=1,TotalPrice=24,Incremental_GCR__c=12,Gross_GCR__c=22,Gross_MRR__c=21,Stretch_MRR__c=11);
            OpportunityLineItem oli3 = new OpportunityLineItem(OpportunityId=oppna[0].Id,PricebookEntryId=pbe[2].Id,UnitPrice=null,Quantity=2,TotalPrice=20,Incremental_GCR__c=12,Gross_GCR__c=22,Gross_MRR__c=21,Stretch_MRR__c=11);
            OpportunityLineItem oli4 = new OpportunityLineItem(OpportunityId=oppna[0].Id,PricebookEntryId=pbe[3].Id,Quantity=2,TotalPrice=20,Incremental_GCR__c=12,Gross_GCR__c=22,Gross_MRR__c=21,Stretch_MRR__c=11);
            OpportunityLineItem oli5 = new OpportunityLineItem(OpportunityId=oppna[0].Id,PricebookEntryId=pbe[4].Id,Quantity=2,TotalPrice=20,Incremental_GCR__c=12,Gross_GCR__c=22,Gross_MRR__c=21,Stretch_MRR__c=11);
            OpportunityLineItem[] oppli = new OpportunityLineItem[]{oli1,oli2,oli3,oli4,oli5};
            insert oppli;
     
Best Answer chosen by Admin (Salesforce Developers) 
SFDC Admin & AnalystSFDC Admin & Analyst

The test methods run in production with all the real time data but the test data is not commited to the database. So when you create a user with a username in your test methods. Do not give common usernames. These username might have not been taken and if taken in future all your test data will be invalid.

 

Thanks


Starz26 wrote:

You will need to break out your trigger SOQL with left/right joins into seperate SOQLs. that use sets for the where clause

 

It may also be this boolean condition:

 

and c.id not in (

select o1.Contract_ID__c From OpportunityLineItem o1
                                                  where o1.Opportunity.IsClosed = False)

 

for isclosed. Try putting an ID=' in there for testing to see if it fixes the error. If it does, you know you will have to rewrite that SOQL

 

See here for details:

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm




All Answers

Starz26Starz26

You will need to break out your trigger SOQL with left/right joins into seperate SOQLs. that use sets for the where clause

 

It may also be this boolean condition:

 

and c.id not in (

select o1.Contract_ID__c From OpportunityLineItem o1
                                                  where o1.Opportunity.IsClosed = False)

 

for isclosed. Try putting an ID=' in there for testing to see if it fixes the error. If it does, you know you will have to rewrite that SOQL

 

See here for details:

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm

SFDC Admin & AnalystSFDC Admin & Analyst

The test methods run in production with all the real time data but the test data is not commited to the database. So when you create a user with a username in your test methods. Do not give common usernames. These username might have not been taken and if taken in future all your test data will be invalid.

 

Thanks


Starz26 wrote:

You will need to break out your trigger SOQL with left/right joins into seperate SOQLs. that use sets for the where clause

 

It may also be this boolean condition:

 

and c.id not in (

select o1.Contract_ID__c From OpportunityLineItem o1
                                                  where o1.Opportunity.IsClosed = False)

 

for isclosed. Try putting an ID=' in there for testing to see if it fixes the error. If it does, you know you will have to rewrite that SOQL

 

See here for details:

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm




This was selected as the best answer