Friday, May 11, 2012

Salesforce - Unable to set OpportunityLineItem Quantity or TotalPrice when using Revenue Schedule

On an Org where the Product has been configured to use Revenue Schedules Salesforce was throwing the following exceptions when the Total Price or Quantity was updated directly on the OpportunityLineItem (that was associated with the Product via a PricebookEntry).

Upsert failed. First exception on row 0 with id 00ke0000000V9BDAA0; first error: FIELD_INTEGRITY_EXCEPTION, field integrity exception: TotalPrice (invalid total price change on opportunity line item): [TotalPrice]

Upsert failed. First exception on row 0 with id 00ke0000000V9BDAA0; first error: FIELD_INTEGRITY_EXCEPTION, field integrity exception: Quantity (invalid quantity change on opportunity line item): [Quantity]

It appears that if the OpportunityLineItem.HasRevenueSchedule is true then these fields will be set via the associated OpportunityLineItemSchedule records.

If this object has a revenue schedule, the Quantity and TotalPrice fields can’t be updated. In addition, the Quantity field can’t be updated if this object has a quantity schedule. Update requests aren’t rejected but the updated values are ignored.
[OpportunityLineItem.HasRevenueSchedule]

To change the Quantity on an OpportunityLineItem where HasRevenueSchedule is true any associated OpportunityLineItemSchedule records need to be deleted first. After the Delete you will need to retrieve the updated OpportunityLineItem to adjust the Quantity on or you can get an error about changing the UnitPrice and TotalPrice:

System.DmlException: Update failed. First exception on row 0 with id 00k4000000MXyoHAAT; first error: INVALID_FIELD, Cannot change both 'UnitPrice' and 'TotalPrice' in update call: [UnitPrice]

OpportunityLineItem oli = [Select Id, UnitPrice, TotalPrice, Quantity from OpportunityLineItem where Id = '00k4000000MXyoHAAT']; // Your OLI id here
OpportunityLineItemSchedule olis = [Select Id from OpportunityLineItemSchedule where OpportunityLineItemId = :oli.Id];

Savepoint sp = Database.setSavepoint();

delete olis;

// If the OLI isn't reloaded from the DB after deleting the OpportunityLineItemSchedule 
// records you will get the DmlException about changing both the UnitPrice and the TotalPrice
// due to the changes the deletion causes
oli = [Select Id, UnitPrice, TotalPrice, Quantity from OpportunityLineItem where Id = '00k4000000MXyoHAAT'];

oli.Quantity = oli.Quantity + 10;
oli.TotalPrice = oli.UnitPrice * oli.Quantity;
update oli;

Database.rollback(sp);

2 comments:

  1. Thanks for the explanation and work-around. I thought we'd have to reconfigure some of our service products when first encountering this but hopefully we'll be able to use your suggestions.

    ReplyDelete
  2. Hi Daniel

    I have a similar problem with Opportunity Product discount. When I try to update the discount, i get the below error:

    FIELD_INTEGRITY_EXCEPTION, Invalid discount change on opportunity line item; cannot modify discount when the item is revenue scheduled: [Discount]

    I followed your approach for Quantity and Total Price, but when I am trying to change the discount using the same approach, I get this error.

    Please let me know if you have any workaround for this.

    ReplyDelete