[SalesForce] FIELD_INTEGRITY_EXCEPTION, This price definition already exists in this price book

My requirement is to take any pricebook entry made in the standard pricebook and replicate/update it in a second price book. With the code below I keep getting the error FIELD_INTEGRITY_EXCEPTION, This price definition already exists in this price book. I have read everything I could find, but I cannot figure out what I need to change.

Any help would be much appreciated. 

global class pricebookEntryCopybatch implements Database.Batchable<sObject>{
        public final string query;
        Set<Id> pbeIds = new Set<Id>();
        Pricebook2 stdPrice = [Select id, isActive from Pricebook2 where isStandard=true limit 1];
        Pricebook2 fdaPrice = [Select id, isActive  from Pricebook2 where Name='FDA Pricebook' limit 1];    
    global pricebookEntryCopybatch(){
      query = 'Select Id, Pricebook2Id, isActive, SystemModStamp from PriceBookEntry ';
  }
        global Database.querylocator start(Database.BatchableContext BC){
                return Database.getQueryLocator(query);
        }


    global void execute(Database.BatchableContext BC, List<sObject> scope){
        for(sObject s : scope){
        for(PricebookEntry pbe : (List<PricebookEntry>)scope){
            if(pbe.Pricebook2Id == stdPrice.Id && pbe.IsActive == true && pbe.SystemModStamp > Datetime.now().addMinutes(-15)){
               pbeIds.add(pbe.id); 
            }
        }

      List<PricebookEntry> pbeforinsert = new List<PricebookEntry>();
            system.debug('***************************************stdPrice ' +stdPrice);
            system.debug('***************************************fdaPrice ' +fdaPrice);
      List<PricebookEntry> stdPBE = [SELECT Pricebook2Id, IsActive, UnitPrice, Id, Product2Id, CurrencyIsoCode FROM PricebookEntry Where Id in: pbeIds];
        for(PricebookEntry pbecopy : stdPBE){
            PricebookEntry pbesToAdd = new PricebookEntry();{
                pbesToAdd.Pricebook2Id = fdaPrice.Id;
                pbesToAdd.IsActive = pbecopy.IsActive;
                pbesToAdd.Product2Id = pbecopy.Product2Id;
                pbesToAdd.UnitPrice = pbecopy.UnitPrice;
                pbesToAdd.CurrencyIsoCode = pbecopy.CurrencyIsoCode;
                pbeforinsert.add(pbesToAdd);

            }
        }
          insert pbeforinsert;  
        }
    }



            global void finish(Database.BatchableContext BC){
   }   


}

Best Answer

You have a number of problems in this code. Most of this stems from the fact that you're querying every price book entry in the database, instead of the ones you're interested in copying:

    global Database.querylocator start(Database.BatchableContext BC){
      return Database.getQueryLocator(
        [SELECT Product2Id, CurrencyIsoCode, UnitPrice 
         FROM PricebookEntry 
         WHERE Pricebook2.IsStandard=true AND IsActive=true AND 
               SystemModStamp >= :DateTime.now().addMinutes(-15)]);
    }

This takes care of your first set of for loops.

Next, we can change the data type on execute so you don't need to do any casting:

global void execute(Database.BatchableContext BC, PricebookEntry[] scope){

Now, you need to deal with actually copying the PricebookEntry items. Realistically, what you need is a Map with a composite key, and you need the products to be updated/inserted, so we need some more variables:

Pricebook2 fdaPrice = [Select id from Pricebook2 where Name='FDA Pricebook'];    
Map<PricebookEntry, PricebookEntry> existing = 
  new Map<PricebookEntry, PricebookEntry>();
PricebookEntry[] inserts = new PricebookEntry[0],
  updates = new PricebookEntry[0];
Set<Id> product2Id = new Set<Id>();
// Aggregate data to query
for(PricebookEntry entry: scope) {
  product2Id.add(entry.Product2Id);
}
// Query for the data we're interested in
for(PricebookEntry entry: [
    SELECT CurrencyIsoCode, Product2Id
    FROM PricebookEntry 
    WHERE Pricebook2.Name = 'FDA Pricebook' AND Product2Id = :product2Id]
) {
  existing.put(
    new PricebookEntry(Product2Id=entry.Product2Id, CurrencyIsoCode=entry.CurrencyIsoCode), 
    entry);
}
// Update our database with the expected values
for(PricebookEntry entry: scope) {
  PricebookEntry fdaentry = existing.get(
    new PricebookEntry(Product2Id=entry.Product2Id, CurrencyIsoCode=entry.CurrencyIsoCode));
  if(fdaentry == null) {
    inserts.add(new PricebookEntry(
      Product2Id=entry.Product2Id,
      Pricebook2Id=fdaPrice.Id, 
      UnitPrice=entry.UnitPrice, 
      CurrencyIsoCode=entry.CurrencyIsoCode,
      IsActive=true));
  } else {
    updates.add(fdaEntry);
    fdaEntry.UnitPrice=entry.UnitPrice;
  }
}
insert inserts;
update updates;

This is the rather common "Aggregate-Query-Update" pattern that I've mentioned from time to time. It's a useful pattern to memorize, because you'll be using it a lot, in triggers, Visualforce pages, batch classes, and anywhere else that you need to process data. Another common term you'd hear is ETL ("Extract, Transform, Load"), but I like the mnemonics of my description better, since it reminds you of what you're actually doing.

Final optimized version:

global class pricebookEntryCopybatch implements Database.Batchable<sObject>{
    global Database.querylocator start(Database.BatchableContext BC){
        return Database.getQueryLocator(
            [SELECT Product2Id, CurrencyIsoCode, UnitPrice 
             FROM PricebookEntry 
             WHERE Pricebook2.IsStandard=true AND IsActive=true AND 
             SystemModStamp >= :DateTime.now().addMinutes(-15)]);
    }
    global void execute(Database.BatchableContext BC, PricebookEntry[] scope){
        Pricebook2 fdaPrice = [Select id from Pricebook2 where Name='FDA Pricebook'];    
        Map<PricebookEntry, PricebookEntry> existing = 
            new Map<PricebookEntry, PricebookEntry>();
        PricebookEntry[] inserts = new PricebookEntry[0],
            updates = new PricebookEntry[0];
        Set<Id> product2Id = new Set<Id>();
        // Aggregate data to query
        for(PricebookEntry entry: scope) {
            product2Id.add(entry.Product2Id);
        }
        // Query for the data we're interested in
        for(PricebookEntry entry: [
            SELECT CurrencyIsoCode, Product2Id
            FROM PricebookEntry 
            WHERE Pricebook2.Name = 'FDA Pricebook' AND Product2Id = :product2Id]
           ) {
               existing.put(
                   new PricebookEntry(Product2Id=entry.Product2Id, CurrencyIsoCode=entry.CurrencyIsoCode), 
                   entry);
           }
        // Update our database with the expected values
        for(PricebookEntry entry: scope) {
            PricebookEntry fdaentry = existing.get(
                new PricebookEntry(Product2Id=entry.Product2Id, CurrencyIsoCode=entry.CurrencyIsoCode));
            if(fdaentry == null) {
                inserts.add(new PricebookEntry(
                    Product2Id=entry.Product2Id,
                    Pricebook2Id=fdaPrice.Id, 
                    UnitPrice=entry.UnitPrice, 
                    CurrencyIsoCode=entry.CurrencyIsoCode,
                    IsActive=true));
            } else {
                updates.add(fdaEntry);
                fdaEntry.UnitPrice=entry.UnitPrice;
            }
        }
        insert inserts;
        update updates;
    }
    global void finish(Database.BatchableContext context) {
    }
}
Related Topic