1

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


}
3
  • 1
    You'll need to split this into two questions, one for the error on price already exists and one for the too many queries error. – gNerb Apr 24 '18 at 15:44
  • I've update this question for just the error on price – Merry Stambaugh Apr 24 '18 at 15:55
  • I regret saying that because the issues are actually linked up basically, your code needed a major overhaul, there's a few answers below that can help you. – gNerb Apr 24 '18 at 16:13
3

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) {
    }
}
2
  • It was the query that I did not know how to get the SystemModStamp to work! I made 2 changes to this, otherwise it worked great. Map<PricebookEntry, PricebookEntry> existingPbes I changed to Map<PricebookEntry, PricebookEntry> existing. And in the query I changed SystemModStamp = :DateTime.now().addMinutes(-15)]) to SystemModStamp > :DateTime.now().addMinutes(-15)]) – Merry Stambaugh Apr 24 '18 at 16:27
  • @MerryStambaugh Yeah, they were oversights. I've edited those changes in. Glad I was able to help you out! – sfdcfox Apr 24 '18 at 16:37
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.