Hi Community

I've got this business problem that we need to have a field on OpportunityLineItem (OLI for short) which represents it's revenue value. But first I need to give you insights on how we set up our model.

We usually have several OLIs on our Opportunities (Opp for short). We grant discounts on 3 levels:
- Difference between List- & Salesprice (= Product Discount, irrelevant here)
- Flat discount amount (= Special Discount Flat)
- Percentage based amount (applied after subtraction of Special Discount Flat, resulting in Special Discount Amount)

Because we often sell our clients packages including multiple product families and we need to get insights on revenue by product family and sales rep, we need this additional information on OLI.

So here's what I do:
On every after trigger action on OLI I recalculate the revenue values of each OLI. On after Opp update with changes in discount fields I invoke the same piece of code including only relevant records (unfinished).
I have a field on OLI that calculates it's share percentage off the Opp amount. So if my OLI would be 2'500 and my Opp amount 10'000 the Share would be 25%. Let's say, this Opportunity also has a Total Discount of 2'000 resulting in 8'000 Opp Amount. Those 2000 need to be distributed over the OLIs according to their share. So the aforementioned OLI would receive 2000 * 25% = 500 of the total discounts as a subtraction to it's value. The resulting revenue value of the OLI would then be 2'500 - 500 = 2'000.

Of course, we have a special case. One Product family may not get Opp discounts applied to unless all OLIs of the Opp are of that product family. So what I do here is gather the OLI share amounts of that product family, add it on top of the total Opp discount and redistribute that total Opp discount on all other OLIs.

I know this is really confusing to imagine so I made a google spreadsheet with a calculation example you can find here (https://docs.google.com/spreadsheets/d/1KZgGShICwAEEqreEt5CNg5ZMinAt165C1qUjOCHNjfI/edit?usp=sharing).

Now my problem is, the code works, but it generates too much inaccuracy. I know that calculating with Double values and percentages will inevitably generate some inaccuracy, but I'd never have imagined that it would generate an inaccuracy of about 3 to 10 on an amount of 7200. Funnily, the inaccuracy seems to be in junction with the Data types I use. I use double  as a main container for values and sub results. However, the Share percentage is in decimal format. I have to transform the percentage (3,15) number into a double.

A strange thing I found out is that if I add floating point zeroes to variables I declare, the revenue value changes into another inaccurate result. Being a double, the amount of zeroes added to the variable during declaration should not matter at all...

Anyway, what I find even more strange is that the google spreadsheet seems to be either more precise or is just intelligent enough to overcome the inaccuracies.

Here is my code:

private void startProcess()
{
	// Based on oppMap Ids, collect all relevant OLIs, exit if nothing to process
	newOLIs = getAllOlis(oppMap.keySet()); // newOLIs is map<Id,OpportunityLineItem>
	// Create the oppOliMap for splitting
	oppOliMap = getOppOliMap(newOlis.values()); // oppOliMap is map<OppId,list<OpportunityLineItem>>
	
	list<OpportunityLineItem> oliUpdate = new list<OpportunityLineItem>();
	
	for ( Id oppId : oppOliMap.keySet() )
	{
		Integer numMDBs = 0;
		Double discountStackMDB = 0.0;
		Double nonMDBShare = 0.0;
		Double nonMDBMultiplier = 0.0;
		
		// Get the number of MDBs and their total discount share
		for ( OpportunityLineItem oli : oppOliMap.get(oppId) )
		{
			if ( oli.ProductFamily__c.contains('MDB') || (oli.ProductFamily__c.contains('Membership') && oli.ProductFamily__c.contains('Designboom')) )
			{
				numMDBs++;
				discountStackMDB += getShareAmount(oppMap.get(oppId), oli.Share__c);
			}
			else
			{
				nonMDBShare += (oli.Share__c.doubleValue() / 100.0);
			}
		}
		
		nonMDBMultiplier = 1.0 / nonMDBShare; 
		
		if ( numMDBs == 0 || numMDBs == oppOliMap.get(oppId).size() )
		{
			for ( OpportunityLineItem oli : oppOliMap.get(oppId) )
			{
				oli.Revenue__c = oli.Salesprice_Total__c - getShareAmount(oppMap.get(oppId), oli.Share__c);
				
				if ( oli.Revenue__c <= 0.0000 && !(oli.Revenue__c == 0.0000 && oli.Salesprice_Total__c == 0.0000) )
				{
					oli.Revenue__c.addError('The System Administrator was not good enough in math and caused this error. Please forgive him and ' 
					+ 'tell him immediately');
				}
				else
				{
					oliUpdate.add(oli);
				}
			}
		}
		else
		{
			for ( OpportunityLineItem oli : oppOliMap.get(oppId) )
			{
				if ( oli.ProductFamily__c.contains('MDB') || (oli.ProductFamily__c.contains('Membership') && oli.ProductFamily__c.contains('Designboom')) )
				{
					oli.Revenue__c = oli.Salesprice_Total__c;
					
					if ( oli.Revenue__c <= 0.0000 && !(oli.Revenue__c == 0.0000 && oli.Salesprice_Total__c == 0.0000) )
					{
						oli.Revenue__c.addError('The System Administrator was not good enough in math and caused this error. Please forgive him and ' 
						+ 'tell him immediately');
					}
					else
					{
						oliUpdate.add(oli);
					}
				}
				else
				{
					oli.Revenue__c = oli.Salesprice_Total__c - getShareAmount(oppMap.get(oppId), oli.Share__c)
					- (discountStackMDB * (oli.Share__c.doubleValue() / 100.0) * nonMDBMultiplier);
					
					if ( oli.Revenue__c <= 0.0000 && !(oli.Revenue__c == 0.0000 && oli.Salesprice_Total__c == 0.0000) )
					{
						oli.Revenue__c.addError('The System Administrator was not good enough in math and caused this error. Please forgive him and ' 
						+ 'tell him immediately');
					}
					else
					{
						oliUpdate.add(oli);
					}
				}
			}
		}
	}
	
	try
	{
		update oliUpdate;
		if ( Test.isRunningTest() )
		{
			System.debug('Throwing Error intentionally');
			insert new Case();
		}
	}
	catch(System.DMLException dex)
	{
		System.debug('There was an error during the OLI Update');
	}
}

Auxiliary method used:

private static Double getShareAmount(Opportunity opp, Decimal share)
{
	Double result = 0.0;
	
	if ( opp.SpecialDiscountFlat__c != NULL )
	{
		result += opp.SpecialDiscountFlat__c;
	}
	if ( opp.Special_Discount_Amount__c != NULL )
	{
		result += opp.Special_Discount_Amount__c;
	}
	// Salesforce treats percentage fields as simple decimals: 7.68% == 7.68 and not 0.0768
	result *= (share.doubleValue() / 100.0);
	return result;
}