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:
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
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.
Post a Comment