FIELD_INTEGRITY_EXCEPTION, This price definition already exists in this price book

Question

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){
   }   


}

Answer 1

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) {
    }
}

Answer 2

I went in to try to edit the post to format your code and found a few issues with it.

1. You looped over scope and then lopped over scope again as a sub loop making it an n^2 operation. The second loop was unnecessary so I removed that loop altogether.
2. You also had queries inside your loop meaning you'd run at least 1 query per PBE in the stnd pricebook so if you had more than 200 pbe you're guaranteed to hit too many sql queries
3. Instead of using PBEids in your queries, they need to be changed to use product2Ids
4. Build a map of product2Id to fdaPBE so you can check if the pbe already exists
5. create a new PBE with the copied over data
6. if it already exists, populate the ID
7. Run upsert instead of insert

Also note, instead of

List<SOBject> identifier = [query];

use

List<SOBject> identifier = new List<SObject>([query]);

This pattern will prevent "List has no rows for assignment to SObject" errors.

Disclaimer I haven't tested this code so there may be some syntax errors but it's much closer than what you have I'm sure.

Apex:

global class pricebookEntryCopybatch implements Database.Batchable<sObject>{
    public final string query;
    Set<Id> product2Ids = 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 (PricebookEntry pbe : (List<PricebookEntry>)scope) {
            if(
                pbe.Pricebook2Id == stdPrice.Id && 
                pbe.IsActive == true && 
                pbe.SystemModStamp > Datetime.now().addMinutes(-15)
            ) {
                product2Ids.add(pbe.Pricebook2Id); 
            }
        }

        List<PricebookEntry> pbeforupsert = new List<PricebookEntry>();

        system.debug('***************************************stdPrice ' +stdPrice);
        system.debug('***************************************fdaPrice ' +fdaPrice);

        List<PricebookEntry> stdPBE = new List<PricebookEntry>([
            SELECT Pricebook2Id, IsActive, UnitPrice, Id, Product2Id, CurrencyIsoCode
            FROM PricebookEntry
            WHERE Product2Id IN :product2Ids
                AND Pricebook2Id = :stdPrice
        ]);

        List<PriceBookEntry fdaPBE = new List<PricebookEntry>([
            SELECT Pricebook2Id, IsActive, UnitPrice, Id, Product2Id, CurrencyIsoCode
            FROM PricebookEntry
            WHERE Product2Id IN :product2Ids
                AND Pricebook2Id = :fdaPrice
        ]);

        Map<Id, PriceBookEntry> productToFDAPBE = new Map<Id, PriceBookEntry>();

        for (PriceBookEntry pbe : fdaPBE) {
            productToFDAPBE.put(pbe.Product2Id, pbe);
        }

        for (PricebookEntry pbecopy : stdPBE) {
            PriceBookEntry tempPBE = new PricebookEntry(
                Pricebook2Id = fdaPrice.Id,
                IsActive = pbecopy.IsActive,
                Product2Id = pbecopy.Product2Id,
                UnitPrice = pbecopy.UnitPrice,
                CurrencyIsoCode = pbecopy.CurrencyIsoCode
            );

            if (productToFDAPBE.containsKey(pbecopy.product2Id)) {
                tempPBE.Id = productToFDAPBE.get(pbecopy.product2Id).id;
            }

            pbeforupsert.add(tempPBE);
        }

        insert pbeforupsert;  
    }

    global void finish(Database.BatchableContext BC){}
}