1

So, about 3 months ago we had someone using the Data Loader to insert some Product2 records, but unfortunately when they imported they didn't realize they duplicated some of our products. Now since then there are some OpportunityLineItems that have accidently referenced the duped Product2Id unaware of the issue.

My question is what is the best way to resolve this. At first I figured the simplest way would be a small little UPDATE script that could be run in execute anonymous could fix this but when we tried that I found out the OpportunityLineItem.Product2Id is not writeable.

[ERROR] Field is not writeable: OpportunityLineItem.Product2Id

Here is an example of what I started.

List <OpportunityLineItem> badOpportunityLineItems = [SELECT Id, Product2Id FROM OpportunityLineItem WHERE Product2Id = '01td0000001OFzn'];

for (OpportunityLineItem o :badOpportunityLineItems) {
    o.Product2Id = '01td000000342CQ';  <== NOT ALLOWED
    system.debug('::opportunityLineItem:: '+ o.Id + ' | ' + o.Product2Id);
}

With this is mind, my current thought is to create a new list of OLIs and DELETE the bad while INSERTING the good like so:

List <OpportunityLineItem> goodpportunityLineItems = new List<OpportunityLineItem>();
for (OpportunityLineItem o :badOpportunityLineItems) {
    OpportunityLineItem new_oli = new OpportunityLineItem();
    new_oli.Product2Id = '01td0000003x307';
    ...
        // map old OLI fields to the new OLD object
    ...
    goodpportunityLineItems.add(new_oli);
}

DELETE badOpportunityLineItems;
INSERT goodOpportunityLineItems;

So I'm asking if this is a good solution, or if there are better options available. Any tweaks, advice, confirmation etc. Thanks!

2 Answers 2

5

The OLI.Product2Id field is instantiated when the OLI is inserted for a given PricebookEntry. The value inserted is the value from Pricebookentry.product2Id

As such, to fix this issue, you will need to delete the existing OLI for any of the duplicate (bad) Product2Id and reinsert the OLI using the correct PriceBookEntry (and hence its corresponding PricebookEntry.Product2Id)

You can't change the PBE on an existing OLI - only delete-insert to get a revised PBE. And you can't edit a PBE to point at a different Product2Id.

As OLI changes will cause the Opportunity.amount to change as well as the HasOpportunityLineItem field to possibly go to false, any workflows that rely on these 'events' (or trigger handlers) will have to be thought through.

2
  • What if the duplicated product is in the same PBE? In this case if I just delete the duped product will the OLIs in question update itself accordingly based on the PBE already being used, almost like a formula field? Jul 17, 2015 at 17:18
  • A PBE is a junction record between a Pricebook2 and a Product2 -- If your bad OLIs refer to a PBE that has an incorrect lookup to a duplicate Product2, then the only solution is to delete the OLI and re-insert it with a PBE that has the correct lookups to the desired Pricebook2 and Product2
    – cropredy
    Jul 17, 2015 at 17:54
1

Here is what we used to solve this problem. We took the duplicate product id (The most recent product created) And we took the oldest Price book entry ID. We also used all 18 character Ids to make sure your SOQL doesn't return false records because it is not case-sensitive.

List <OpportunityLineItem> badOpportunityLineItems = [SELECT Id, Product2Id, PricebookEntryId,Description,OpportunityId,Quantity,UnitPrice,TotalPrice FROM OpportunityLineItem 
WHERE Product2Id = '(Newest Product ID)'];
List <OpportunityLineItem> goodOpportunityLineItems = new List <OpportunityLineItem>();

for (OpportunityLineItem o :badOpportunityLineItems) {
    OpportunityLineItem newLineItem = new OpportunityLineItem();
    newLineItem.PricebookEntryId = '(Oldest Price book entry)';

    newLineItem.Description = o.Description;
    newLineItem.OpportunityId = o.OpportunityId;
    newLineItem.Quantity = o.Quantity;
    newLineItem.UnitPrice = o.UnitPrice;

    /* Any custom fields you need to include */
    newLineItem.Custom_Fields = o.Custom_Fields__c;

    /* Add new line item to you new list/collection */
    goodOpportunityLineItems.add(newLineItem);

}

DELETE badOpportunityLineItems;
INSERT goodOpportunityLineItems;

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .