A Few Observations on Using Custom Pricing Plugins Alongside Project Service Automation

The problem sometimes, when developing a bespoke solution, is that it is impossible to anticipate how it may evolve. Whether it becomes dependent on changed functionality or new systems introduced into the equation, this eventuality is not necessarily down to a scoping or requirements gathering error. Organisations can, and will, drive off into all sorts of unexpected directions over time and, like it or lump it, IT systems are generally the last to follow behind any change. In this week’s post, I want to focus on a recent example I was involved in related to this theme, involving Dynamics 365 Customer Engagement (D365CE) and the Project Service Automation (PSA) add-on.

Setting the Scene: What is a Custom Pricing Plug-in?

Those familiar the D365CE will no doubt be able to explain how the application automatically totals up the various product line items for core, sales-related entities - Opportunity, Quote etc. Typically, this behaviour will be suitable for most scenarios, but there may be situations where fine-tuning is required to factor in additional calculations or perform a bespoke integration. For example, there may be a requirement to query an external ERP system to ensure that the Cost Price value for a line item record is correct. And, while it is undoubtedly possible to customise the application around how the default pricing calculations work, solutions of this nature can typically become unwieldy to maintain over time. Besides, you always remain at the mercy of how the application decides to perform sales entity calculations, which may be subject to change between major version releases of D365CE.

Fortunately, one of (I believe) the best-kept secrets concerning this functionality is that a) it can be disabled entirely and b) replaced with new pricing logic of your choosing. The CalculatePrice message is the C#/VB.NET developer’s gateway towards achieving this and, by developing a bespoke plug-in, it is possible to tailor all sales calculations completely. While implementing a solution of this nature does, invariably, introduce a degree of complexity into your D365CE deployment, it does have several benefits:

Avoids a messy/“hacky” solution from being implemented using Business Rules, Flows or any other customisation feature within the application.
Provides the capability to apply the same calculations to all sales entities quickly or implement bespoke logic to calculate Quotes differently from Opportunity records, for example.
Allows you to achieve more complex integration requirements, such as those involving separate application systems.

I am a major proponent of implementing custom pricing solutions and have discussed how to get started with custom pricing plug-ins previously on the blog. However, I do always heavily caution their use, and it is a “last resort” if you cannot work with D365CE’s default pricing functionality. The old maxim stands true: just because you CAN do something, doesn’t necessarily mean you should 🙂

PSA + Custom Pricing Plugin = ?

Going back now to the example I alluded to at the start of this post…I was contacted by a customer who was having issues with a custom pricing solution I had implemented for them a while back. Since then, the organisation had implemented PSA and had started to get very nasty error messages appearing when working with Project-based Line records, as indicated below:

As the error message demonstrates, the culprit was the custom pricing plug-in, which was preventing the creation of any associated Line Detail record. Evaluating the tracing logs for this plug-in showed that everything worked fine and dandy, up until the point when any actual calculation values needed applying to the Quote Line record. This step would fail with the following error message:

CalculatePrice: System.ServiceModel.FaultException`1[Microsoft.Xrm.Sdk.OrganizationServiceFault]: The total line amount and estimated tax of all Sales Document Line Details must equal the line amount and estimated tax of the Sales Document Line. (Fault Detail is equal to Exception details:

Further investigation of the error logs pointed towards the PSA managed solution plug-in Microsoft.Dynamics.ProjectService.Plugins.PreValidatequotedetailUpdate being the culprit for the raising of the OrganisationServiceFault error in the first place.

Understanding How PSA Works

At this stage, I had to get to grips with how this nice-looking PSA application worked under the hood, having previously only a surface-level awareness of its behaviour. PSA is designed to work very closely alongside the existing Sales functionality within D365CE. This approach provides not only the ability to manage/plan projects at a resource-level but ensures the appropriate factoring of any delivery costs into generated sales records. For “time & material” projects, this is crucial, as providing sufficient accounting for a resources time and financial return is imperative. For the most part, PSA relies on the tried and tested Sales module functionality to deliver most of these requirements, but also provides the following, additional Line Detail entities, linked to the corresponding sales/product line entity:

Opportunity Line Detail
Quote Line Detail
Sales Contract (i.e. Order) Line Detail
Invoice Line Detail

The purpose of these entities (except for the Opportunity Line Detail, which records a “finger in the air” budget value instead) is to allow multiple resources to be associated with a Quote Line record. For example, let’s assume you are charging a customer for Project Scoping. As part of this, the following resources need to be assigned:

4 hours for a Solution Architect
6 hours for a Pre-Sales Consultant
6 hours for a Project Manager

In this scenario, all three of these roles would be created as Line Detail records against a Project Scoping Line record. Although this process does involve a few additional steps compared to the default Sales module, this functionality is also useful as it allows you to:

Factor in whether a resource is chargeable as part of an activity or not.
Allows you to categorise the type of cost that the Line Detail is.
Indicate the anticipated length of time that the resource will require involvement as part of an engagement.

So with a combination of understanding how the application works and a thorough diagnosis of the error message in question, the answer to the problem seems relatively apparent. Any PSA custom pricing plug-in must factor the total, chargeable value of all related Line Detail’s, ensuring the Amount and Estimated Tax fields update accordingly.

The Techie Bit: Evaluating a Suggested Approach

Now that we know the problem, we can start to think about a potential resolution. The C# class below shows a potential approach for calculating PSA Product Line details records. Pass any Line record through, and the pesky error message shown above should no longer appear, and all expected calculation will be applied:

private static void CalculatePSAProduct(Entity e1, IOrganizationService service, ITracingService tracingService)
{
  //Determine which Line Detail records to retrieve from - throw an error if an unexpected record has been passed through.
  //Also need to determine the lookup field name as part of this operation - either msdyn_quotelineid or msdyn_salescontractlineid
  string detailName;
  string detailRelatedEntityName;
  
  switch (e1.LogicalName)
  {
    case "quotedetail":
      detailName = "msdyn_quotelinetransaction";
      detailRelatedEntityName = "msdyn_quotelineid";
      tracingService.Trace("Record being calculated = Quote Line Detail");
      break;
    case "salesorderdetail":
      detailName = "msdyn_orderlinetransaction";
      detailRelatedEntityName = "msdyn_salescontractlineid";
      tracingService.Trace("Record being calculated = Order Line Detail");
      break;
    default:
      throw new InvalidPluginExecutionException("An error occurred when applying custom pricing. An incorrect entity name was passed to the CalculatePSAProduct method");
  }
  
  //Retrieve all related, chargeable Line Detail records that have a Transaction Type value of "Project Contract" with the Amount, Tax and amount_after_tax field values

  tracingService.Trace("Retrieving all related, chargeable " + detailName + " records...");
  QueryByAttribute qba = new QueryByAttribute(detailName);
  qba.ColumnSet = new ColumnSet("msdyn_amount", "msdyn_tax", "msdyn_amount_after_tax");
  qba.Attributes.AddRange("msdyn_billingtype");
  qba.Values.AddRange(192350001);
  qba.Attributes.AddRange("msdyn_transactiontypecode");
  qba.Values.AddRange(192350004);
  qba.Attributes.AddRange(detailRelatedEntityName);
  qba.Values.AddRange(e1.Id);

  EntityCollection lt = service.RetrieveMultiple(qba);
  tracingService.Trace("Got " + lt.Entities.Count.ToString() + " " + detailName + " records!");

  //Total up each individual Line Detail record to determine Amount, Estimated Tax and Amount After Tax
        
  decimal ppu = 0;
  decimal t = 0;
  decimal ea = 0;

  for (int i = 0; i < lt.Entities.Count; i++)
  {
    //If no value in the returned fields, default to 0
    ppu += ((Money)lt.Entities[i]["msdyn_amount"])?.Value ?? 0;
    t += ((Money)lt.Entities[i]["msdyn_tax"])?.Value ?? 0;
    ea += ((Money)lt.Entities[i]["msdyn_amount_after_tax"])?.Value ?? 0;
  }
	
  //From here, you can begin to apply any custom calculations needed to satisfy your individual requirements.
	//In this example, we simply pass the totals from above onto the relevant Line fields and then update the Line record.

  e1["priceperunit"] = new Money(ppu);
  tracingService.Trace("Quoted/Contracted Amount  = " + ppu.ToString());
  e1["tax"] = new Money(t);
  tracingService.Trace("Estimated Tax  = " + t.ToString());
  e1["extendedamount"] = new Money(ea);
  tracingService.Trace("Quoted/Contracted Amount After Tax  = " + ea.ToString());

  service.Update(e1);
  tracingService.Trace(e1.LogicalName + " updated successfully!");
}

Some further explanation on this class may be required, given some additional quirks that PSA throws into the mix:

The code is designed to be Line Detail record agnostic, which is why a switch statement is needed to determine certain field values needed within the QueryByAttribute query. The Opportunity Line and Invoice Detail entities do not behave the same way as the Quote & Order Line entities, so these are specifically excluded.
It would appear that the PSA application creates several, hidden Line Detail records in the application for every Line Detail record that a user creates. These blank records do not contain any of the expected pricing values. Therefore, to avoid potential issues, the QueryByAttribute filters specifically ensure these do not return and, also, ensures that only records marked as chargeable are brought back.

Beyond that, most of the code should be self-explanatory for those familiar working with C# - however, let me know in the comments below if you have any queries.

All Change Ahead

At the time of writing this post, we have just recently seen a major upgrade of PSA take place, from version 2 to version 3. This particular release saw a lot of change within PSA and, indeed, users of version 2 of the application need to specifically request Microsoft to allow them to upgrade to the newer version. This requirement is likely due to the risk of stuff breaking between versions. Now, I am hearing on the grapevine that another significant upgrade will be coming as part of the fall 2019 release. A reading of the release notes would seem to suggest this to be a major overhaul, likely involving the removal entirely of the current Microsoft Project embedded functionality. We will no doubt find out more about this soon, but I would caveat this entire post by saying it’s outlined fix could become redundant within months. All we can do for now is keep a keen eye on the release notes and see how things ultimately pan out.

The Community is Awesome

In times of crisis new and exciting learning opportunities, you can always rest assured that the fantastic D365CE community will be on hand to assist. In this case, I must give thanks to the PSA wizard Antti Pajunen for being a reliable and authoritative steer on PSA and in helping to answer my questions relating to it. If you are working with PSA and are not already following his blog, then get over there today - you won’t be disappointed!

I Could Talk All Day About This Stuff…

No, seriously, I could. Feel free to leave a comment below or contact me if you would like to find out how to implement a custom pricing solution within your D365CE system.