FIELD_INTEGRITY_EXCEPTION, The price book entry is Inactive

Question

I'm a bit stumped. Everything was working fine for a few months and then errors like this started popping up, any idea how to fix it?

Here's the trigger :

trigger RenewalOppTrigger on Opportunity (after insert, after update) {

   List<Opportunity> oppLst = [SELECT Id,
                               Name,
                               StageName, 
                               Date_of_Loss__c, 
                               AccountId,
                               LeadSource,
                               Amount,
                               CloseDate,
                               Parent_Opportunity__c,
                               Payment_Period__c,
                               Payment_Method__c,
                               Subscription_End_Date__c,
                               Subscription_Start_Date__c,
                               Renewal_Opp_Created__c
                               FROM Opportunity WHERE Id IN:Trigger.new];

   for (Opportunity opp: oppLst) {
    // Reload the Opp from DB so that we can use this class in 'after' 
    // triggers.
    RenewalOppCreator creator = new RenewalOppCreator(opp);
    //THIS IS LINE 22 RIGHT BELOW
    creator.run();
   }
}

Here's the code :

public class RenewalOppCreator {
public class MpoweredPricebookEntryMissingException extends Exception {}

Opportunity originalOpp;
public String renewalOppId;

public RenewalOppCreator(Opportunity inputOpp) {
    this.originalOpp = inputOpp;
}

public static Pricebook2 getPricebookForYear(Integer year) {
    Pricebook2 pb;
    try{
        // Get pricebook for year of renewal
        return pb = [SELECT Id FROM Pricebook2 WHERE Year__c =:year LIMIT 1];
    } catch(Exception ex){
        // Or get standard pricebook
        return pb = [SELECT Id FROM Pricebook2 WHERE IsStandard=true];
    }
}

public static void bootstrapAllOpps() {
    List<Opportunity> oppLst = [Select Name, 
                                StageName, 
                                Date_of_Loss__c, 
                                AccountId,
                                LeadSource,
                                Amount,
                                CloseDate,
                                Parent_Opportunity__c,
                                Signed_Quote_Uploaded__c,
                                Subscription_End_Date__c,
                                Subscription_Start_Date__c,
                                Renewal_Opp_Created__c
                                from Opportunity];

    for(Opportunity opp : oppLst) {
        RenewalOppCreator creator = new RenewalOppCreator(opp);
        creator.run();
    }

}

void removeOldRenewalOpp() {
    List<Opportunity> oppLst = [Select Id, Parent_Opportunity__c from Opportunity where Parent_Opportunity__c =: this.originalOpp.Id];
    delete oppLst;
}

boolean needsRenewal() {
    if(this.originalOpp.StageName == 'Won' && 
       this.originalOpp.Date_of_Loss__c == NULL &&
       this.originalOpp.Subscription_End_Date__c != NULL &&
       this.originalOpp.Subscription_Start_Date__c != NULL &&
       !this.originalOpp.Renewal_Opp_Created__c) {
       return true;
    } else {
       return false;
    }
}

Date endOfTheMonth(Date dateOfInterest) {
    Integer numberOfDays = Date.daysInMonth(dateOfInterest.year(), dateOfInterest.month());
    return Date.newInstance(dateOfInterest.year(), dateOfInterest.month(), numberOfDays);
}

Integer subscriptionLengthInMonths() {
    return this.originalOpp.Subscription_Start_Date__c.monthsBetween(this.originalOpp.Subscription_End_Date__c) + 1;
}

Date nextSubscriptionEndDate() {
    return endOfTheMonth(this.originalOpp.Subscription_End_Date__c.addMonths(subscriptionLengthInMonths()));
}

Date nextSubscriptionStartDate() {
    return endOfTheMonth(this.originalOpp.Subscription_End_Date__c).addDays(1);
}

Id renewalRecordTypeId () {    
    return Schema.SObjectType.Opportunity.getRecordTypeInfosByName().get('Renewal').getRecordTypeId();
}

public boolean run() {
    if (!this.needsRenewal()) {
        return false;
    }

    removeOldRenewalOpp();

    Opportunity renewalOpp = new Opportunity();

    renewalOpp.Name = this.originalOpp.Name;
    renewalOpp.StageName = 'Accepted';
    renewalOpp.RecordTypeId = renewalRecordTypeId();
    renewalOpp.Type = 'Existing Business';
    renewalOpp.Subscription_Start_Date__c = nextSubscriptionStartDate();
    renewalOpp.Subscription_End_Date__c = nextSubscriptionEndDate();
    renewalOpp.AccountId = this.originalOpp.AccountId;
    renewalOpp.LeadSource = this.originalOpp.LeadSource;
    renewalOpp.Amount = this.originalOpp.Amount;
    renewalOpp.Payment_Period__c = this.originalOpp.Payment_Period__c;
    renewalOpp.Payment_Method__c = this.originalOpp.Payment_Method__c;
    renewalOpp.CloseDate = renewalOpp.Subscription_Start_Date__c;
    renewalOpp.Parent_Opportunity__c = this.originalOpp.Id;

    Database.SaveResult srList = Database.insert(renewalOpp);

    if (srList.isSuccess()) {
        this.originalOpp.Renewal_Opp_Created__c = true;
        this.renewalOppId = renewalOpp.Id;

        update this.originalOpp;

        //THIS IS LINE 113 RIGHT BELOW
        new RenewalQuoteCopier(renewalOpp, originalOpp).copySyncedQuote();

        return true;
    } else {
        return false;
    }
}


class RenewalQuoteCopier {
    Opportunity renewalOpp;
    Opportunity originalOpp;

    public RenewalQuoteCopier(Opportunity ropp, Opportunity oopp) {
        this.renewalOpp = ropp;
        // requery this so that we don't have to pass in objects with read-only fields in memory (like SyncedQuote)
        this.originalOpp = [Select Id, SyncedQuoteId from Opportunity where Id=:oopp.Id];
    }

    private boolean hasOriginalQuote() {
        return originalOpp.SyncedQuoteId != NULL;
    }

    private Quote getOriginalQuote() {            
        return [SELECT ID, Name, OpportunityId FROM Quote WHERE Id=:this.originalOpp.SyncedQuoteId];
    }

    private void copyQuoteDetails(Quote copiedQuote, Quote originalQuote) {
        copiedQuote.Status = 'Draft';
        copiedQuote.Name = originalQuote.Name;
        copiedQuote.ExpirationDate = this.renewalOpp.Subscription_Start_Date__c;
        copiedQuote.OpportunityId = this.renewalOpp.Id;
    }

    private void setPriceBook(Quote copiedQuote) {
        Pricebook2 pb = RenewalOppCreator.getPricebookForYear(copiedQuote.ExpirationDate.year());
        copiedQuote.Pricebook2Id = pb.Id;
    }

    private void copyQuoteLineItems(Quote copiedQuote, Quote originalQuote) {
        List<QuoteLineItem> oldQLIList = new List<QuoteLineItem>();
        List<QuoteLineItem> newQLIList = new List<QuoteLineItem>();

        oldQLIList = [SELECT Id, Quantity, Product2Id, Discount FROM QuoteLineitem WHERE QuoteId=:originalQuote.Id];

        for(QuoteLineItem oli: oldQLIList){
            Product2 p = [SELECT Id, Name, Generally_Renewed__c FROM Product2 WHERE Id=:oli.Product2Id];

            if(p.Generally_Renewed__c) {
                QuoteLineItem nli = new QuoteLineItem();

                nli.QuoteId = copiedQuote.Id;
                nli.Quantity = oli.Quantity;
                nli.Product2Id = oli.Product2Id;
                nli.Discount = oli.Discount;

                PricebookEntry pbe;

                try {
                    pbe = [SELECT Id, UnitPrice FROM PricebookEntry WHERE Pricebook2Id=:copiedQuote.Pricebook2Id AND Product2Id=:nli.Product2Id];
                } catch(System.QueryException e) {
                    Pricebook2 pb = [SELECT Id, Name, Year__c FROM Pricebook2 WHERE Id=:copiedQuote.Pricebook2Id];
                    throw new MpoweredPricebookEntryMissingException('Pricebook entry missing in pricebook: ' + pb.Name + ' (year: ' + pb.Year__c + ')' + ', for product: '+ p.Name + ' (Id: ' + nli.Product2Id + '). You will need to rebuild the renewal opp for Opp: ' + originalQuote.OpportunityId); 
                }

                nli.PricebookEntryId = pbe.Id;
                nli.UnitPrice = pbe.UnitPrice;

                newQLIList.add(nli);
            }
        }
        // THIS IS LINE 184 RIGHT BELOW
        insert newQLIList;
    }

    private void copySyncedQuote() {

        System.debug('In copySyncedQuote');
        if(!hasOriginalQuote()) {
            return;   
        }
        System.debug('Copying synced quote');
        // set pricebook
        Pricebook2 pb = RenewalOppCreator.getPricebookForYear(renewalOpp.Subscription_Start_Date__c.year());
        renewalOpp.Pricebook2Id = pb.Id;
        update renewalOpp;

        Quote originalQuote = getOriginalQuote();
        Quote copiedQuote = new Quote();

        copyQuoteDetails(copiedQuote, originalQuote);
        setPriceBook(copiedQuote);

        //THIS IS LINE 207 RIGHT BELOW
        insert copiedQuote;

        copyQuoteLineItems(copiedQuote, originalQuote);

        RenewalQuoteSyncer.synceQuote(copiedQuote.Id, renewalOpp.Id);
    }

}
}

Answer 1

In your method

public static Pricebook2 getPricebookForYear(Integer year) {
    Pricebook2 pb;
    try{
        // Get pricebook for year of renewal
        return pb = [SELECT Id FROM Pricebook2 WHERE Year__c =:year and IsActive = true LIMIT 1];
    } catch(Exception ex){
        // Or get standard pricebook
        return pb = [SELECT Id FROM Pricebook2 WHERE IsStandard=true];
    }
}

Add the filter IsActive = true in SOQL because it looks like you are querying the Pricebook record which is inactive. So you need to filter them using this filter.

Answer 2

This error occurs when we are trying to insert OpportunityLineItem/ QuoteLineItem with inactive PriceBookEntry/PriceBook.

There are following workaround for the same (choose best suited as per your business):

Interrupt transaction with Notifying the Inactive PriceBook Entries details: Show error message that this transaction can not be completed as the Old Quote has n number of Quote Line Items (you found with inactive price book entries). In this case, there will need to perform a manual activity to active needed records.

Without Interrupting the flow: You can add two additional DML operations over here. Active all the required inactive price book entries referred by old QLIs. Insert QLIs and again revert the price book entries to the original inactive state. However, QLI with inactive PriceBookEntry does not make sense.