Our first step here is introduce the notion of a Tax Code to MSCRM via a new custom entity – this could be a simple additional screen comprising of a simple ‘Name’, ‘Rate’ and ‘Is Default for Organisation?’ fields to form a short entity data dictionary:
Impression of the Tax Code Entity's possible Data Dictionary
The new Tax Code entity could then be related to other entities in CRM as per the following ERD:
Entity Relationship Diagram showing how the new Tax Code Entity may relate to the other system Entities in MSCRM
This then gives several methods for deducing a default tax code for an Invoice or Invoice Line:
(1) The Product’s Default Tax Code for an Invoice Line
(2) The Tax Code assigned for an Invoice based on the Customer Account
(3) The Default Tax Code for the MSCRM Deployment Organisation
This list is in order of typical priority – such that certain Products sold by an Organisation will have different Tax Levels (such as 0% Sales Tax on Services for example), and Accounts working in different countries or different currencies may have different default Tax Codes as a result.
Once the Tax Code entity has been published and a list of Tax Codes defined – our next step is to add a layer of logic to MSCRM that will automatically assign a Tax Code to a new Invoice (based on Customer Account and then the Default), and similarly assign a Tax Code to each Invoice Line (based on Product and then Invoice).
List of Tax Codes in Settings area of MSCRM
This is where we would need a pair of custom Plugins to support this for assigning a Tax Code to an Invoice and Invoice Line accordingly.
Invoice Plugin to assign Tax Code on creation of a new Invoice
The following Plugin code would then assign a Tax Code to a new Invoice when an Invoice is first created, this Plugin is envisaged to only fire on Create as any subsequent changes to an Invoice or the Customer paying the Invoice would be intended to be manually updated by the CRM System User.
public void Execute(IPluginExecutionContext cxt)
{
try
{
DynamicEntity targetMessage = null;
string typeName = string.Empty;
#region Get Target
// Check if the input parameters property bag contains a target
// of the create operation and that target is of type DynamicEntity.
if (cxt.InputParameters.Properties.Contains("Target") &&
cxt.InputParameters.Properties["Target"] is DynamicEntity)
{
// Obtain the target business entity from the input parmameters.
targetMessage = (DynamicEntity)cxt.InputParameters.Properties["Target"];
typeName = targetMessage.Name;
if (typeName != "invoice")
{
// plugin is valid for this entity
throw new Exception("TaxCodeAssignment_Invoice Plugin is not valid for the '" + typeName + "' entity type");
}
}
else
{
throw new Exception("No Target Entity in Plugin Context");
}
#endregion
#region Create instance of CRM Methods class to invoke any Data Access requests to/from MSCRM
PluginCRMMethods crmMethods = null;
if (cxt.InvocationSource == 0)
{
// Parent Pipeline
crmMethods = new PluginCRMMethods(cxt.CreateCrmService(true));
}
else
{
throw new Exception("Unknown Plugin Invocation Source " + cxt.InvocationSource.ToString() + "");
}
#endregion
#region Business Logic
Guid taxCodeId = Guid.Empty;
// Get details primarily from Target Message
// TARGET- Invoice already has specified Tax Code Id
if (targetMessage.Properties.Contains("new_taxcodeid"))
{
Microsoft.Crm.Sdk.Lookup new_taxcodeid = (Microsoft.Crm.Sdk.Lookup)targetMessage.Properties["new_taxcodeid"];
taxCodeId = new_taxcodeid.Value;
}
// TARGET- determine whether a Tax Code can be assigned from the Customer selected for the Invoice
else if (targetMessage.Properties.Contains("customerid"))
{
Microsoft.Crm.Sdk.Customer customerid = (Microsoft.Crm.Sdk.Customer)targetMessage.Properties["customerid"];
Guid accountId = Guid.Empty;
if (customerid.type == "contact")
{
// read up from Contact to determine Account
accountId = crmMethods.GetAccountFromContact(customerid.Value, true);
}
else if (customerid.type == "account")
{
accountId = customerid.Value;
}
if (!(accountId.Equals(Guid.Empty)))
{
taxCodeId = crmMethods.GetDefaultTaxCodeFromAccount(accountId);
}
}
else
{
throw new Exception("No Customer Id for Invoice?");
}
// TARGET- alternatively use organisation's default Tax Code if no other could be determined
if (taxCodeId.Equals(Guid.Empty))
{
taxCodeId = crmMethods.GetDefaultTaxCode();
}
if (taxCodeId.Equals(Guid.Empty))
{
throw new Exception("No Tax Code specified for Invoice Line - must have a Tax Code");
}
else
{
if (!(targetMessage.Properties.Contains("new_taxcodeid")))
{
LookupProperty property = new LookupProperty();
property.Name = "new_taxcodeid";
property.Value = new Lookup("new_taxcode", taxCodeId);
targetMessage.Properties.Add(property);
}
else
{
Lookup new_taxcodeid = (Lookup) targetMessage.Properties["new_taxcodeid"];
new_taxcodeid.Value = taxCodeId;
}
}
#endregion
}
catch (Exception ex)
{
string errMessage = "TaxCodeAssignmentInvoice.Execute (" + ex.Message + ")";
throw new Exception(errMessage);
}
}
NOTE: This code (and all the Plugin examples detailed here) is dependent on a separate ‘PluginMethods’ class for invoking Data Access operations against the MSCRM Web Service – the code for this is posted in the third post for handling Tax Codes.
This Plugin code could then be registered as a Pre-Event Step on the Create Message of the Invoice entity in the Parent Pipeline.
Invoice Line Plugin to assign Tax Code on create or update of an Invoice Line
The following Plugin code is an idea of how to assign a Tax Code to an Invoice Line when the Invoice Line is created or updated – this Plugin then also gives the Invoice Line a % Tax Rate based on the Tax Code assigned.
This percentage is the first step to extending MSCRM to automatically calculating the amount of tax for the Invoice Line and so giving the Net and Gross figures for each Invoice Line based on the Tax Code assigned to the line based on the Product or Invoice involved.
public void Execute(IPluginExecutionContext cxt)
{
try
{
DynamicEntity targetMessage;
DynamicEntity preImage = null;
string typeName = string.Empty;
#region Get Target
// Check if the input parameters property bag contains a target
// of the create operation and that target is of type DynamicEntity.
if (cxt.InputParameters.Properties.Contains("Target") &&
cxt.InputParameters.Properties["Target"] is DynamicEntity)
{
// Obtain the target business entity from the input parmameters.
targetMessage = (DynamicEntity)cxt.InputParameters.Properties["Target"];
typeName = targetMessage.Name;
if (typeName != "invoicedetail")
{
// plugin is valid for this entity
throw new Exception("TaxCodeHandling Plugin is not valid for the '" + typeName + "' entity type");
}
}
else
{
throw new Exception("No Target Entity in Plugin Context");
}
#endregion
#region Get PreImage (if preUpdate Message)
if ( cxt.MessageName == "Update" )
{
// Check if the input parameters property bag contains a target
// of the create operation and that target is of type DynamicEntity.
if (cxt.PreEntityImages.Contains("PreImage") &&
cxt.PreEntityImages["PreImage"] is DynamicEntity)
{
// Obtain the target business entity from the input parmameters.
preImage = (DynamicEntity)cxt.PreEntityImages["PreImage"];
if (preImage.Name != "invoicedetail")
{
// plugin is valid for this entity
throw new Exception("TaxCodeHandling Plugin is not valid where Pre Image is for the '" + preImage.Name + "' entity type");
}
}
else
{
throw new Exception("No Preimage in Plugin Context for Update Message");
}
}
#endregion
#region Create instance of CRM Methods class to invoke any Data Access requests to/from MSCRM
PluginCRMMethods crmMethods = null;
if (cxt.InvocationSource == 0)
{
// Parent Pipeline
crmMethods = new PluginCRMMethods(cxt.CreateCrmService(true));
}
else
{
throw new Exception("Unknown Plugin Invocation Source " + cxt.InvocationSource.ToString() + "");
}
#endregion
#region Business Logic
Guid taxCodeId = Guid.Empty;
// Get details from Pre-Image where possible
if (preImage != null)
{
// PREIMAGE- Invoice Line already has specified Tax Code Id
if (preImage.Properties.Contains("new_taxcodeid"))
{
Microsoft.Crm.Sdk.Lookup new_taxcodeid = (Microsoft.Crm.Sdk.Lookup)preImage.Properties["new_taxcodeid"];
taxCodeId = new_taxcodeid.Value;
}
// PREIMAGE- determine whether a Tax Code can be assigned from the Product selected for the Invoice Line
else if (preImage.Properties.Contains("productid"))
{
Microsoft.Crm.Sdk.Lookup productid = (Microsoft.Crm.Sdk.Lookup)preImage.Properties["productid"];
taxCodeId = crmMethods.GetTaxCodeFromProduct(productid.Value);
}
// PREIMAGE- alternatively determine whether a Tax Code can be assigned from the Parent Invoice for the Invoice Line
else if (preImage.Properties.Contains("invoiceid"))
{
Microsoft.Crm.Sdk.Lookup invoiceid = (Microsoft.Crm.Sdk.Lookup)preImage.Properties["invoiceid"];
taxCodeId = crmMethods.GetTaxCodeFromInvoice(invoiceid.Value);
}
}
// Get details primarily from Target Message
// TARGET- Invoice Line already has specified Tax Code Id
if (targetMessage.Properties.Contains("new_taxcodeid"))
{
Microsoft.Crm.Sdk.Lookup new_taxcodeid = (Microsoft.Crm.Sdk.Lookup)targetMessage.Properties["new_taxcodeid"];
taxCodeId = new_taxcodeid.Value;
}
// TARGET- determine whether a Tax Code can be assigned from the Product selected for the Invoice Line
else if (targetMessage.Properties.Contains("productid"))
{
Microsoft.Crm.Sdk.Lookup productid = (Microsoft.Crm.Sdk.Lookup)targetMessage.Properties["productid"];
taxCodeId = crmMethods.GetTaxCodeFromProduct(productid.Value);
}
// TARGET- alternatively determine whether a Tax Code can be assigned from the Parent Invoice for the Invoice Line
else if (targetMessage.Properties.Contains("invoiceid"))
{
Microsoft.Crm.Sdk.Lookup invoiceid = (Microsoft.Crm.Sdk.Lookup)targetMessage.Properties["invoiceid"];
taxCodeId = crmMethods.GetTaxCodeFromInvoice(invoiceid.Value);
}
if (taxCodeId.Equals(Guid.Empty))
{
Microsoft.Crm.Sdk.Lookup invoiceid = (Microsoft.Crm.Sdk.Lookup)targetMessage.Properties["invoiceid"];
throw new Exception("No Tax Code specified for Invoice Line attached to Invoice '" + invoiceid.Value.ToString() + "' - must have a Tax Code");
}
else
{
// get Tax Rate based on assigned Tax Code
decimal taxRate = crmMethods.GetTaxRateFromTaxCode(taxCodeId);
if (!(targetMessage.Properties.Contains("new_taxcodeid")))
{
LookupProperty property = new LookupProperty();
property.Name = "new_taxcodeid";
property.Value = new Lookup("new_taxcode", taxCodeId);
targetMessage.Properties.Add(property);
}
if (targetMessage.Properties.Contains("new_taxrate"))
{
Microsoft.Crm.Sdk.CrmDecimal taxRateProperty = (Microsoft.Crm.Sdk.CrmDecimal)targetMessage.Properties["new_taxrate"];
taxRateProperty.Value = taxRate;
}
else
{
Microsoft.Crm.Sdk.CrmDecimal taxRateProperty = new CrmDecimal(taxRate);
Microsoft.Crm.Sdk.CrmDecimalProperty property = new CrmDecimalProperty();
property.Name = "new_taxrate";
property.Value = taxRateProperty;
targetMessage.Properties.Add(property);
}
}
#endregion
}
catch (Exception ex)
{
string errMessage = "TaxCodeAssignment.Execute (" + ex.Message + ")";
// throw error such that it is show in the Microsoft CRM Workflow Tools
throw new Exception(errMessage);
}
}
This Plugin code could then be registered as a Pre-Event Step on both the Create and Update Messages of the Invoice Detail entity in the Parent Pipeline.
Invoice Line Plugin to calculate the amount of Tax on create or update of an Invoice Line
The following Plugin code takes the Tax Code and Rate assigned to an Invoice Line and then calculates the amount of Tax based on the (calculated by MSCRM default business logic) Base Amount of the Invoice Line. This logic must take place on the Post step of the Create or Update of the Invoice Line as the Base Amount or Price per Unit fields will not have been populated by the MSCRM Platform Layer until the Post step – this does leave the Plugin triggering on Update of an event whilst also updating the same event, so we must be careful of accidentally triping an infinite loop here.
public void Execute(IPluginExecutionContext cxt)
{
try
{
DynamicEntity postImage;
string typeName = string.Empty;
#region Get Post Image
// Check if the input parameters property bag contains a target
// of the create operation and that target is of type DynamicEntity.
if (cxt.PostEntityImages.Contains("PostImage") &&
cxt.PostEntityImages["PostImage"] is DynamicEntity)
{
// Obtain the target business entity from the input parmameters.
postImage = (DynamicEntity)cxt.PostEntityImages["PostImage"];
if (postImage.Name != "invoicedetail")
{
// plugin is valid for this entity
throw new Exception("TaxCalculation Plugin Post Image is not valid for the '" + postImage.Name + "' entity type");
}
}
else
{
throw new Exception("No Post Image Entity in Plugin Context");
}
#endregion
#region Create instance of CRM Methods class to invoke any Data Access requests to/from MSCRM
PluginCRMMethods crmMethods = null;
if (cxt.InvocationSource == 0)
{
crmMethods = new PluginCRMMethods(cxt.CreateCrmService(true));
}
else
{
throw new Exception("Unknown Plugin Invocation Source " + cxt.InvocationSource.ToString() + "");
}
#endregion
#region Business Logic
decimal taxRate = 0;
decimal baseAmount = 0;
decimal taxCurrent = 0;
decimal taxCurrentMin = 0;
decimal taxCurrentMax = 0;
decimal taxCalculated = 0;
Guid taxCodeId = Guid.Empty;
// get tax code from invoice line
if (postImage.Properties.Contains("new_taxcodeid"))
{
Microsoft.Crm.Sdk.Lookup new_taxcodeid = (Microsoft.Crm.Sdk.Lookup)postImage.Properties["new_taxcodeid"];
taxCodeId = new_taxcodeid.Value;
}
if (postImage.Properties.Contains("new_taxrate"))
{
taxRate = ((CrmDecimal)postImage.Properties["new_taxrate"]).Value;
}
else if (!(taxCodeId.Equals(Guid.Empty)))
{
taxRate = crmMethods.GetTaxRateFromTaxCode(taxCodeId);
}
else
{
taxRate = 0;
}
if (postImage.Properties.Contains("baseamount"))
{
baseAmount = ((CrmMoney)postImage.Properties["baseamount"]).Value;
}
if (postImage.Properties.Contains("tax"))
{
taxCurrent = ((CrmMoney)postImage.Properties["tax"]).Value;
taxCurrentMin = taxCurrent - (1 / 100);
taxCurrentMax = taxCurrent + (1 / 100);
}
taxCalculated = (baseAmount / 100) * taxRate;
if ((taxCalculated > taxCurrentMax) || (taxCalculated < taxCurrentMin))
{
// tax values do not match
if (postImage.Properties.Contains("invoicedetailid"))
{
Guid invoiceDetailId = ((Key)postImage.Properties["invoicedetailid"]).Value;
crmMethods.UpdateTaxAmount(invoiceDetailId, taxCalculated);
}
else
{
throw new Exception("Invoice Line does not have a GUID Id for 'invoicedetailid'");
}
}
#endregion
}
catch (Exception ex)
{
string errMessage = "Execute (" + ex.Message + ")";
throw new Exception(errMessage);
}
}
MSCRM’s default business logic will then take the updated amount of Tax and include this figure to produce a revised Extended Amount for the Invoice Line – and therefore giving the Invoice Line Net (Base Amount) and Gross (Extended Amount) figures.
This Plugin code could then be registered as a Post-Event Step on both the Create and Update Messages of the Invoice Detail entity in the Parent Pipeline. To avoid the infinite loop risk, we should register the Update Step to only fire on certain field updates:
Registering the Tax Code Calculation Plugin using the MSCRM Plugin Registration Tool on certain fields to avoid creating an infinite loop
This, whilst quite simple in itself, actually builds a relatively detailed process flow for recording a new Invoice Line.
| 1. | New Invoice Line XML Message is initially communicated to the MSCRM Platform Layer. | |
| 2. | Tax Code Assignment Plugin is triggered as a result of the Create Message prior to the Message being communicated to the MSCRM Data Layer – the Plugin Logic determines which Tax Code and Tax Rate to assign to the Invoice Line and adds the values into the Create Message XML accordingly. | |
| 3. | MSCRM Default Logic calculates the Base Amount for the Invoice Line based on the Price per Unit * Quantity. | |
| 4. | Create Message delivered to MSCRM Data Layer and Invoice Detail created as a result – with relevant Tax Code, Tax Rate and Base Amount values. | |
| 5. | Tax Calculation Plugin is triggered as a result of the Create Message after creation in the MSCRM Data Layer – the Plugin Logic examines the values for Tax Code, Tax Rate and Base Amount to determine whether to update the Invoice Detail record with a new Tax Amount value. If so, the MSCRM Web Service is invoked to update the Invoice Detail record with the new Tax Amount. | |
| 6. | If updated, this then in turn communicates a new XML Message to the MSCRM Platform Layer to update the Invoice Line with the correct amount of Tax. | |
| 7. | MSCRM Default Logic calculates a revised Extended Amount for the Invoice Line based on the revised Tax Amount. | |
| 8. | Update Message delivered to MSCRM Data Layer and the Invoice Line’s Gross/Extended amount updated as a result. | |
| 9. | Execution now finishes with a final Invoice Line reflecting the automatically assigned Tax Code, Tax Rate, Tax Amount and Extended Amount. |
Tax Code Handling Plugins as registered in the MSCRM Plugin Registration Tool
However this process flows provides a straight-forward User Experience when adding a Invoice Line to an Invoice:
Creating an Invoice Line in MSCRM - Prior to Save
Creating an Invoice Line in MSCRM - After Save
This gives us a simple method for users to add Invoice Lines to an Invoice that incorporate an automatically assigned Tax Code that then determines the Tax Rate and Amount involved to give a gross amount for each Invoice Line and the overall Invoice involved.
However this method does not incorporate for when a Invoice is added to an existing MSCRM Sales Order where the Order Lines are transfered into the Invoice as Invoice Lines – here the Plugin logic detailed above would not fire due to the differences between the MSCRM Parent Pipeline and Child Pipeline, the next post will examine this and how the problem can be resolved.
CRM Consultancy Blog 
Hi,
these are really interesting posts and we are numerous to face these development to tweak CRM’s pricing calculations.
But you said :
NOTE: This code (and all the Plugin examples detailed here) is dependent on a separate ‘PluginMethods’ class for invoking Data Access operations against the MSCRM Web Service – the code for this is posted in the third post for handling Tax Codes.
The thing is I can’t find this post (third one for handling tax codes). Is it published? Have any URL I haven’t seen?
Thanks a lot!
Denis
Hi Denis,
Aye – this code uses a simple library called PluginMethods for reading data back from MSCRM, and inserting or updating data. So is mostly simple Data Access methods hitting the MSCRM Web Service, will post up this evening so that might help you out.
Kind Regards, Paul.
Hi Paul,
thanks for your quick answer.
I was referring to this method particularly:
I faced a similar design but based on modifying extendedamount on Quote/Order/Invoice entities and I had to make sure of what you exactly say here:
You are in parent pipeline and I had to recall business entity and update it through a call to crm service (not the context one) and I don’t see this happening in your plugin. Why is it so?
Again, thanks and kind regards,
Denis
I forgot to name the method:
crmMethods.UpdateTaxAmount(invoiceDetailId, taxCalculated);
Hi Denis,
MSCRM is a bit strange in that updating the ‘extendedamount’ field via the MSCRM Webservice will usually be ignored or your overwritten by MSCRM’s core logic which insists ExtendedAmount = (Unit Price * Qty) – Discount + Tax; and so updating this field manually will usually not work for putting a Tax model in place.
The idea here was to incorporate a system where the Tax field is automatically calculated by the additional Plugin Logic (based on a selected or assigned Tax Code) such that this calculation then fits into the normal MSCRM logic.
The awkward bit comes when MSCRM is automatically creating the Invoice Lines in the child pipeline and so capturing the event for updating the Tax Code and Tax amount fields is a bit more tricky. Hope that helps.
Yes, I’ve been seeing just a bit of the complexity of all intrications with child pipeline plugins.
Would you mind me asking you another question?
If you have a customer request to round the extendedamount to something specific (like 1 decimal but displaying 2), how would you manage the post step plugin to apply rounding ? It’s going to be a tricky one for me because it’s a real demand I have.
It is difficult to change any aspect of the ‘extendedamount’ field in MSCRM – the field settings for decimal places is fixed as a system field, and the logic that calculates the field is fixed by the internal MSCRM Business Logic.
To workaround this, you would probably look to amend the Discount field on the Order Line entity via a Plugin with a limited discount that effectively rounded the ‘extendedamount’ to the nearest £0.5 or £0.1 banding.
Such that if we have a Order Line with the following values:
unitprice = £4.18
quantity = 11
baseamount = £45.98
Therefore the ‘manualdiscountamount’ field is automatically calculated via a Plugin to round off the £45.98 to either £46.00 or £45.50 depending on how you needed your rounding logic.
manualdiscountamount = £-0.02 or £0.48
extendedamount = £46.00 or £45.50
(you can customise the ‘manualdiscountamount’ field to allow for negative values)
So whilst you always will end up showing the 2 decimals for the extended amount, the second decimal would always be 0.
The Plugin here for this logic would need to operate in the child pipeline to support Orders resulting from Opportunities if this is how the business process works for your project – if the business process is simply recording Orders directly, you could simplify this via using the parent pipeline or even form-scripting.
Hope that helps.
Thanks!
I tried updating extendedamount through a plugin, using a Math.Floor( x *10) / 10 with no luck and I am heading to your solution, with both JScript and a plugin.
Parent pipe should be just fine here.
Again, thanks for your post: I am a NAV guy initially and I find similarities between you post and NAV’s behaviour.
All the best
Pingback: CRM Plugin, Moniker, Pipeline -Kevin's Mocha
I am writing a tax plug-in form CRM 2011. But I cant get the BASEAMOUNT from context using this code :
PrecioUnitario = (Money)postImage.Attributes[“baseamount”];
I tried using a query, but it is always 0… What am I doing wrong ??
// Create query using querybyattribute
QueryByAttribute queryBaseAmount = new QueryByAttribute(“quotedetail”);
queryBaseAmount.ColumnSet = new ColumnSet(“baseamount”);
tracingService.Trace(“Columnset de Detalle preparado: “);
// Attribute to query
queryBaseAmount.Attributes.AddRange(“quotedetailid”);
tracingService.Trace(“Atributo por el cual buscar asignado”);
// Value of queried attribute to return
queryBaseAmount.Values.AddRange(entidad.Id);
tracingService.Trace(“Valor de busqueda asignado quotedetailId : ” + entidad.Id);
// Query passed to the service proxy
EntityCollection detailRetrieved = service.RetrieveMultiple(queryBaseAmount);
tracingService.Trace(“Servicio de busqueda creado para Detalle”);
// Iterate through returned collection
foreach (var p in detailRetrieved.Entities)
{
PrecioUnitario = (Money)p.Attributes[“baseamount”];
tracingService.Trace(“BASEAMOUNT : ” + PrecioUnitario.Value);
}