locked
Need help overriding the unit price of a Opportunity Product RRS feed

  • Question

  • It appears that there is no way within the UI to override the price of an opportunity product like you can for quotes, orders and invoices.  A real mistep on MS part if you ask me, since you create quotes based off of the products in the opportunity.

    I found a blog on the Internet that talked about creating some new attributes and then creating a db trigger to change the values for the unitprice based on what you put in the new attributes.  It seems like it should work.  It seemed to have some errors when trying to create a trigger using his code, so I modified it to work in my instance.  When I choose to update the price, and save the opportunity product, it shows the new price in the unit price field, my amount, and extended price also show up correctly.  The problem is when I generate a quote from the opportunity, it puts the products in the quote, but defaults back to the pricelist pricing for the item, instead of using the overridden price that I specified.  I have set the ispriceoverridden attribute to true.

    Here is the sql trigger that I am creating, any idea why it will not use the overridden price to generate the quote?  I have the check in the sql query for my guid so that the trigger only affects me for now.
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [dbo].[UpdateOpportunityProductPrice]
    ON [dbo].[OpportunityProductExtensionBase]
    AFTER INSERT, UPDATE
    AS
    
    DECLARE @opportunityProductId uniqueidentifier
    
    SELECT @opportunityProductId = i.OpportunityProductId FROM inserted i;
    
    IF @opportunityProductId IS NOT NULL
    BEGIN
    DECLARE @new_priceoverridden bit
    DECLARE @new_priceperunit money
    DECLARE @new_amount money
    DECLARE @new_extendedamount money
    
    
    SELECT @new_priceoverridden = i.new_priceoverridden,
    @new_priceperunit = i.new_priceperunit, @new_amount = opb.Quantity * i.new_priceperunit,
    @new_extendedamount = (opb.Quantity * i.new_priceperunit) - isnull(opb.manualdiscountamount,'0.00')
    FROM inserted i inner join dbo.opportunityproductbase opb on i.opportunityProductId = opb.opportunityProductId
    
    IF @new_priceoverridden = 1
    BEGIN
    UPDATE dbo.OpportunityProductBase
    SET extendedamount = isnull(@new_extendedamount,0), 
    	priceperunit = isnull(@new_priceperunit,0),
    	ispriceoverridden = 1,
    	baseamount = isnull(@new_amount,0)
    WHERE opportunityProductId = @opportunityProductId and createdby = 'C8E81AE0-C74E-DD11-91F0-0015C55D2501'
    END
    END
    Any idea why the data is not staying when passing from opportunity to quote?
    Thursday, December 31, 2009 7:01 PM

Answers

  • My speculation is not so much they forgot to put it in as they ran out of time. A number of features were restricted on release due to a lack of testing time.

    Have you considered not assigning products to the opportunity but having the user enter the total amount and then add products when you convert to a quote?

    Leon Tribe

    Want to hear me talk about all things CRM? Check out my blog

    http://leontribe.blogspot.com/

    or hear me tweet @leontribe
    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    • Proposed as answer by Leon TribeMVP Thursday, December 31, 2009 9:55 PM
    • Marked as answer by Jim Glass Jr Monday, January 4, 2010 3:27 PM
    Thursday, December 31, 2009 9:55 PM

All replies

  • As a word of warning, even if you get this working it will be an unsupported customisation given it uses SQL triggers.

    Leon Tribe

    Want to hear me talk about all things CRM? Check out my blog

    http://leontribe.blogspot.com/

    or hear me tweet @leontribe
    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    Thursday, December 31, 2009 7:45 PM
  • Would not have to do this if MS had put this ability in.  Why would they have the ability to override the price on quote, order and invoice, but not opportunity?  Did someone just forget about it?

    We have support plans that have a custom value, no two are the same.  So when we put it in the opportunity, we cannot change the price to what the price of the plan is.  So that throws off the estimated value of the opportunity.  Then when we make a quote, we have to remember to go in and override the price at that was carried over from the opportunity.

    I figured it would not be supported though.  Just have to figure out if there is a way to make crm use the values passed from the opportunity product instead of going back to the price list to get the prices.  I have tried updating the mappings, but it does not seem to matter.

    conteplating a second trigger that triggers on a quote detail record being created, so that it checks to see if there is a corrosponding opportunity product record, and if so, is it marked as overridden, and if so, then overrides the value that it pulled in from the price list with the one that is in the opportunity product record.
    Thursday, December 31, 2009 8:20 PM
  • Hi,

    Instead of writting a trigger in the database, my suggestion would be to create a plugin on the quote.  Then have this plugin correct the price of the quote all via web services.  The plugin, via code, should be able to look at any field in the opportunity and correct the price in the quote.

    Alex Fagundes - www.PowerObjects.com
    Thursday, December 31, 2009 9:10 PM
  • My speculation is not so much they forgot to put it in as they ran out of time. A number of features were restricted on release due to a lack of testing time.

    Have you considered not assigning products to the opportunity but having the user enter the total amount and then add products when you convert to a quote?

    Leon Tribe

    Want to hear me talk about all things CRM? Check out my blog

    http://leontribe.blogspot.com/

    or hear me tweet @leontribe
    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    • Proposed as answer by Leon TribeMVP Thursday, December 31, 2009 9:55 PM
    • Marked as answer by Jim Glass Jr Monday, January 4, 2010 3:27 PM
    Thursday, December 31, 2009 9:55 PM