+ Start a Discussion
Matt FieldMatt Field 

Need help updating Opportunity revenue calculation trigger

I have the following trigger on the Opportunity object to perform revenue calculations, and I need help making these updates:

 

1.  Change when the trigger is fired from the current:  Trigger.new[i].Opportunity.LastModifiedDate != Trigger.old[i].LastModifiedDate to use the LastModifiedDate from OpportunityLineItem or OpportunityLineItemSchedule instead.

 

2.  I have to add a new field to the Opportunity object to calculate first year revenue (FTV__c).  This calculation is different from the one in the trigger in that this calculation has to look at the first 12 months of revenue and use that number to populate FTV__c.  The First_Year_Revenue__c field in the trigger only looks at the revenue in the calendar year.

 

trigger reestablishServiceSchedule on Opportunity (after update) {

    Set<Id> oppIds = new Set<Id>();
    List<OpportunityLineItemSchedule> schedules = new List<OpportunityLineItemSchedule>();
    List<OpportunityLineItem> olis = new List<OpportunityLineItem>();

    if(!ProcessorControl.isTriggered){
        
        for(Integer i = 0; i < Trigger.new.size(); i++){
            if(Trigger.new[i].LastModifiedDate!=Trigger.old[i].LastModifiedDate){
                oppIds.add(Trigger.new[i].Id);
            }
        }
        
        
        for (OpportunityLineItem oli: [Select Id, PricebookEntry.Product2.Family, PricebookEntry.Product2.Name, Opportunity.Billing_Date__c, ServiceDate, First_Year_Revenue__c, Second_Year_Revenue__c, Third_Year_Revenue__c, Fourth_Year_Revenue__c, (Select Id, ScheduleDate, Revenue From OpportunityLineItemSchedules Order By ScheduleDate Asc) From OpportunityLineItem Where HasRevenueSchedule = true AND Opportunity.ID IN: oppIds]){
            Date closeDate = oli.Opportunity.Billing_Date__c;
            for(Integer i = 0; i < oli.OpportunityLineItemSchedules.size(); i++){
                oli.OpportunityLineItemSchedules[i].ScheduleDate = closeDate.addMonths(i);
                schedules.add(oli.OpportunityLineItemSchedules[i]);
            }
            oli.ServiceDate = oli.Opportunity.Billing_Date__c;
            olis.add(oli);
        }
        update olis;
        update schedules;
        
        olis = new List<OpportunityLineItem>();
        for (OpportunityLineItem oli: [Select Id, Opportunity.Billing_Date__c, First_Year_Revenue__c, Second_Year_Revenue__c, Third_Year_Revenue__c, Fourth_Year_Revenue__c, TotalPrice, HasRevenueSchedule, (Select Id, ScheduleDate, Revenue From OpportunityLineItemSchedules Order By ScheduleDate Asc) From OpportunityLineItem Where Opportunity.ID IN: oppIds]){
            double firstYearRevenue = 0;
            double secondYearRevenue = 0;
            double thirdYearRevenue = 0;
            double FourthYearRevenue = 0;
            if(oli.HasRevenueSchedule){
                for(OpportunityLineItemSchedule sched: oli.OpportunityLineItemSchedules){
                    if(oli.Opportunity.Billing_Date__c.year() == sched.ScheduleDate.year()){
                            firstYearRevenue += sched.Revenue;
                    } else if(oli.Opportunity.Billing_Date__c.year()+ 1 == sched.ScheduleDate.year()){
                            secondYearRevenue += sched.Revenue;
                    } else if(oli.Opportunity.Billing_Date__c.year()+ 2 == sched.ScheduleDate.year()){
                            thirdYearRevenue += sched.Revenue;
                    } else if(oli.Opportunity.Billing_Date__c.year() + 3 == sched.ScheduleDate.year()){
                            FourthYearRevenue += sched.Revenue;
                    }
                }
            } else {
                firstYearRevenue = oli.TotalPrice;
            }
            oli.First_Year_Revenue__c = firstYearRevenue;
            oli.Second_Year_Revenue__c = secondYearRevenue;
            oli.Third_Year_Revenue__c = thirdYearRevenue;
            oli.Fourth_Year_Revenue__c = FourthYearRevenue;
            olis.add(oli);
        }
        update olis;
    }

}

 Any help provided will be GREATLY appreciated.

 

Thanks,

 

Matt

MTBRiderMTBRider

I am not quite understanding what you are asking.  For your number 1, Trigger.new[i].Opportunity.LastModifiedDate != Trigger.old[i].LastModifiedDate is always going to be true since you would not be in the trigger if something did not change.   Are you saying that your logic needs to be in the OpportunityLineItem trigger instead?

 

As for your number 2, again not sure what you are asking.  Add the fields that you need to add and the repost with the specific bit of code that you have the question about. 

Matt FieldMatt Field

For number 1,  I am looking to use last modified date from OpportuntityLineItem rather than Opportunity because if someone goes in and updates the revenue in OpportunityLineItems, it doesn't update the LastModifiedDate in Opportunity.

 

For number 2 I am pretty lost.  I am trying to track just the first 12 months of revenue and update the FTV__c field in Opportunity, but I have no idea where to start with it or where it should go.

 

Thanks

MTBRiderMTBRider

So why not put the trigger code in the opportunitylineitem trigger instead of the opportunity trigger?

 

For the revenue calculation, can you query for all of the line items for each opportunity in the trigger and sum up revenue in a for loop that loops 12 times (I assume that the first month of revenue is different across any given set of opportunities)?

Matt FieldMatt Field

I don't know why it was put in Opportunity instead of OpportunityLineItem.  This was done before I started working on the system.  I can move the trigger if that makes the most sense.  Would moving it to OpportunityLineItem effect the fact that the calculations are being shown on the Opportunity object?

MTBRiderMTBRider

If changes to opportunity line item is what is suppose to make this code run (as opposed to changes to opportunities), then it makes sense to have the code in the opportunitylineitem trigger.  You can still update, insert, etc opportunity records or other objects from the opportunityline item trigger.

 

As for the 12 month revenue calculation, doesn't First_year_Revenue__c in the OpportunityLineItem table (which is already being calculated in your trigger) contain that value already?  If not, how does it differ from the value that is suppose to be calculated for FTV__c?

 

Matt FieldMatt Field

Ok, I will move the trigger over to the OpportunityLineItem object.

 

The First_Year_Revenue__c field calculates revenue from the billing date thru the end of the year.  The FTV_c field will need to calculate revenue from the billing date thru 12 months.  Most of our products sell with a ramp-up period, so month 1 may be $1,000, month 2 $3,000, month 3 $4,000 etc.  So if the billing date is 6/1/13, the FTV field would have to calculate thru 6/1/14.

 

Thanks