Monday, July 13, 2009

Override Price - Opportunity Product MSCRM 4.0

During implanting Microsoft CRM 4.0 I came across an issue to override price on opportunity product. Out of box CRM4.0 doesn’t allow to override price on opportunity product. I tried couple of options like setting ispriceoverridden=true using javascript, a server side plugin to change price but no luck. Then a workaround worked which I would like to share with all of you. Workaround is a trick to use outbox box “Manual Discount” field (only allowed field where user can write something) to implement override price.


Out of box calculation of ExtendedAmount is
ExtendedAmount = (PricePerUnit * Qty) ManualDiscount

Example (Out of Box calculation):-
Product: XYZ
Price (Pricelist): $15
Qty: 10
Manual Discount: $10
Extended Amount: $140 = (($15 * 10) - $10)

In above example you cannot override price as calculation is done on server. Any changes in Price here will be overridden with pricelist on server.

Below are steps to allow price override opportunity product.

a) Add custom fields on Opportunity Product

a. New_priceoverridden (bit)– custom field to know is price overriddden

b. New_priceperunit (money) – custom field to save priceperunit

c. New_manualdiscount (money) – custom field to save manual discount.

d. New_amount (money)– custom field to save amount

b) Add above custom fields on form and move out of box fields to hidden tab

c) Add finally a database trigger which does a trick

/****** Object: Trigger [dbo].[UpdateOpportunityProductPrice]

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_manualdiscount money
DECLARE @new_priceperunit money
DECLARE @new_amount money

SELECT @new_priceoverridden = i.new_priceoverridden,
@new_manualdiscount = i.new_manualdiscount,
@new_priceperunit = i.new_priceperunit,
@new_amount = i.qty * i.priceperunit
FROM inserted i

IF @new_priceoverridden = 0
BEGIN


UPDATE dbo.OpportunityProductBase
SET new_amount = @new_amount, manualdiscountamount = @new_manualdiscount,
extendedamount = isnull(baseamount,0) - isnull(manualdiscountamount,0)
WHERE opportunityProductId = @opportunityProductId


END
ELSE
BEGIN


UPDATE dbo.OpportunityProductBase
SET new_amount = @new_amount, manualdiscountamount = (isnull(baseamount,0) - isnull(@new_amount,0)) + isnull(@new_manualdiscount,0),
extendedamount = isnull(@new_amount,0) - isnull(@new_manualdiscount,0)
WHERE opportunityProductId = @opportunityProductId


END
END

Verifying above example after implementing workaround

Example:-
Product: XYZ
Price Overridden (Custom field): Yes
Price (Custom Field): $25 (price in pricelist is $15)
Qty: 10
Manual Discount (Custom Field): $10

calculation takes place in database trigger
Manual Discount (out of box) = -$110 = $150 - (($25 * $10) + $10))
Extended Amount: $260 = (($15 * 10) - (-$110))

Product: XYZ
Price Overridden (Custom field): No
Price (Custom Field): $15 (price from pricelist)
Qty: 10
Manual Discount (Custom Field): $10

calculation takes place in database trigger
Manual Discount (out of box) = $10
Extended Amount: $140 = (($15 * 10) - $10)

d) Change Opportunity Product – Quote Product relationship mapping to map custom fields instead of outbox fields. Unofficial way to change relationship mapping as Mapping is not displayed in Opportunity Product – Quote Product relationship

a) Get EntityMapId from database

SELECT EntityMapId, TargetEntityName, SourceEntityName FROM EntityMap WHERE (TargetEntityName like '%quotedetail%') AND (SourceEntityName like '%opportunityproduct%');

b) Copy EntityMapId from above query result

http://crmurl/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=D3644E14-657B-DD11-9769-001E0B4882E2

Please make sure above mappingid will be EntityMapId from query result.


2 comments:

Unknown said...

While executing the trigger, I am getting error:
Invalid column name 'qty'.
Invalid column name 'priceperunit'.

Do we have to create a new attribute for quantity (new_quantity) also as there is no attribute in opportunityextensionbase table with name quantity.
And in place of 'priceperunit' we have to mention new_priceperunit.

Kindly revert

Ritesh Sutaria said...

Neha,

column 'qty' and 'priceperunit' are in opportunityproductbase table so you need to join opportunityproductbase table to get those fields.

Let me know if you still have any issues.