Solved Code for rescheduling opportunityLineItemSchedule when changing CloseDate on Opportunity

Question

Resolved code for updating OpportunityLineItemSchedule ScheduleDate to new CloseDate when close date is changed on Opportunity object. Also to update OpportunityLineItem ServiceDate on change of Opportunity CloseDate Trigger:

    trigger OpportunityReScheduling on Opportunity (after update, before update, after insert) 
    {
        for (Opportunity o: Trigger.new)
        {
            if (Trigger.isBefore)
            {
                Opportunity oldCloseDate = Trigger.oldMap.get(o.ID);
                if (o.CloseDate != oldCloseDate.CloseDate) 
                {
                    Integer Oldday = Integer.valueOf(oldCloseDate.CloseDate.Day()); 

                    Integer day = Integer.valueOf(o.CloseDate.Day());
                    Integer month = Integer.valueOf(o.CloseDate.Month());
                    Integer year = Integer.valueOf(o.CloseDate.Year());                 

                    Integer DayDiff = day - Oldday;

                    OpportunitySchedulingHandler.ScheduleDateUpdate(o.id, day, month, year, DayDiff);
                }
            }

            if (Trigger.isAfter || Trigger.isInsert)
            {
                OpportunitySchedulingHandler.ScheduleServiceDateUpdate(o.id);   
            }
        }
    } 

Class:

    public with sharing class OpportunitySchedulingHandler {

     //Update LineItemSchedule dates for all scheduling dates

    public static void ScheduleDateUpdate(String oppid, Integer day, Integer month, Integer year, Integer DayDiff) 
    {
       List<OpportunityLineItem> idval = [SELECT id FROM OpportunityLineItem WHERE OpportunityId=:oppid];
       List<OpportunityLineItemSchedule> datelist = new List<OpportunityLineItemSchedule>();
       for (Integer i = 0; i < idval.size(); i++)
       {
           datelist = [SELECT ScheduleDate FROM OpportunityLineItemSchedule WHERE OpportunityLineItemId =:idval[i].id];
           for (Integer k = 0; k < datelist.size(); k++)
           {
               if(DayDiff <> 0)
               {
                //automatic functionality actually adds 24 list (12 for quantity 12 for revenue)
                //editing schedule for quantity
                if(k<12)
                {
                    Date mydate = date.newInstance(year,month,day);
                    datelist[k].ScheduleDate = mydate.addmonths(k);
                }
                //editing schedule for revenue
                else
                {
                    Date mydate = date.newInstance(year,month,day);
                    datelist[k].ScheduleDate = mydate.addmonths(k-12);
                }
               }
            }
           if(!datelist.isEmpty())
           {
                update datelist;
           }
        }
    }    

      //Update ServiceDate with closeDate

    public static void ScheduleServiceDateUpdate(String oppid)
    {
        List<Opportunity> oliList = [SELECT Id, Name, CloseDate, (SELECT Id, ServiceDate, OpportunityId from OpportunityLineItems) from Opportunity where Id =:oppid];
        List<OpportunityLineItem> oliUpdateList = new List<OpportunityLineItem>();
        for(Opportunity x : oliList)
        {
            for(OpportunityLineItem oli : x.OpportunityLineItems)
            {
                oli.ServiceDate = x.CloseDate;
                oliUpdateList.add(oli);
            }
        }
        if(!oliUpdateList.isEmpty()) 
        {
            update oliUpdateList;
        }
    }
} 

Test Class:

@isTest
private class OpportunityReSchedulingTest {

    static testMethod void myUnitTest() {
        // TO DO: implement unit test
        Test.starttest();

            Account acc = new Account();
            acc.Name='NewAcc';
            insert acc;

            Opportunity opp = new Opportunity();
            opp.Name='NewOpp';
            opp.AccountId=acc.Id;

            opp.StageName='Prospecting';
            opp.CloseDate=Date.today().addDays(10);

            insert opp;

            Product2 Prod =  new Product2();
            Prod.Name='NewProd';
            Prod.IsActive=True;
            insert Prod;

            PricebookEntry pbe = new PricebookEntry();
            pbe.Product2Id=Prod.Id;
            pbe.IsActive=True;
            pbe.UnitPrice=70;
            pbe.Pricebook2Id = Test.getStandardPricebookId();
            pbe.UseStandardPrice=false;

            insert pbe;

            OpportunityLineItem opli = new OpportunityLineItem();
            opli.UnitPrice = 57;
            opli.Quantity = 12;
            opli.OpportunityId=opp.Id;
            opli.PricebookEntryId=pbe.id;
            insert opli;

            opp.CloseDate = opp.CloseDate.addDays(20);
            update opp;


        Test.stoptest();
    }
}

Note:

Scheduling might give an error if OpportunityLineItemSchedule will be created manually (using establish/Re-establish function) for more than 12 schedules.

Answer 1

Thanks for this original code, it has been such a helpful base for us. I thought I would share some fixes we have applied, they might help someone out there.

This is when revenue scheduling isn't the norm, but needs to be an option for your organisation, so the default product schedule is 1 line item.

Here is our fix. This removes the issue of the duplication of the line item.

Trigger:

trigger OpportunityReScheduling on Opportunity (after update, before update, after insert) 
    {
        for (Opportunity o: Trigger.new)
        {
            if (Trigger.isBefore)
            {
                Opportunity prevOpportunity = Trigger.oldMap.get(o.ID);
                if (o.CloseDate != prevOpportunity.CloseDate) 
                {
                    Integer DayDiff = prevOpportunity.CloseDate.daysBetween(o.CloseDate);

                    OpportunitySchedulingHandler.ScheduleDateUpdate(o.id, DayDiff);
                }
            }

            if (Trigger.isAfter || Trigger.isInsert)
            {
                OpportunitySchedulingHandler.ScheduleServiceDateUpdate(o.id);   
            }
        }
    }

Class:

public with sharing class OpportunitySchedulingHandler {

     //Update LineItemSchedule dates for all scheduling dates

    public static void ScheduleDateUpdate(String oppid, Integer DayDiff) 
    {
       List<OpportunityLineItem> idval = [SELECT id FROM OpportunityLineItem WHERE OpportunityId=:oppid];
       List<OpportunityLineItemSchedule> datelist = new List<OpportunityLineItemSchedule>();
       for (Integer i = 0; i < idval.size(); i++)
       {
           datelist = [SELECT ScheduleDate FROM OpportunityLineItemSchedule WHERE OpportunityLineItemId =:idval[i].id];
           Date firstDate = datelist[0].ScheduleDate.addDays(DayDiff);
           datelist[0].ScheduleDate = firstDate;

           Integer day = firstDate.day();
           Integer month = firstDate.month();
           Integer year = firstDate.year();

           for (Integer k = 1; k < datelist.size(); k++)
           {
               Integer nYear = year;
               Integer nMonth = month + k;
               Integer nDay = day;

               if (nMonth > 12) {
                   nMonth = nMonth - 12;
                   nYear = nYear + 1;
               }

               Set<Integer> longMonths = new Set<Integer> {1,3,5,7,8,10,12};

               if (nDay == 31 && ! longMonths.contains(nMonth)) {
                   nDay = 30;
               }

               if (nDay > 28 && nMonth == 2) {
                   nDay = 28;
               }

               Date mydate = date.newInstance(nYear,nMonth,nDay);
               datelist[k].ScheduleDate = mydate;
           }
           if(!datelist.isEmpty())
           {
                update datelist;
           }
        }
    }    

      //Update ServiceDate with closeDate

    public static void ScheduleServiceDateUpdate(String oppid)
    {
        List<Opportunity> oliList = [SELECT Id, Name, CloseDate, (SELECT Id, ServiceDate, OpportunityId from OpportunityLineItems) from Opportunity where Id =:oppid];
        List<OpportunityLineItem> oliUpdateList = new List<OpportunityLineItem>();
        for(Opportunity x : oliList)
        {
            for(OpportunityLineItem oli : x.OpportunityLineItems)
            {
                oli.ServiceDate = x.CloseDate;
                oliUpdateList.add(oli);
            }
        }
        if(!oliUpdateList.isEmpty()) 
        {
            update oliUpdateList;
        }
    }
}

I hope this is helpful to someone out there!

James

Answer 2

I think the problem you're having is in the way you're getting the DateDiff value.

The close date can change, but the day of the month it changes to, may not change at all. The original date may be on Oct, 30 2015, and the new close date may be Dec 30, 2015. The dateDiff should be 61 days. However, you've been using a method that calculates it by getting the day of the month for both the old and the new. Unless the day of the month changes, you'll show no difference.

What if the difference is say 95 days and your current day of the month is 29? If you were calculating it correctly, your method wouldn't accommodate that. You'd be creating actually creating a new instance of an invalid date rather than a date that's 3 months out.You'll see where I've fixed that using the daysBetween method as shown below:

 if (Trigger.isBefore)
            {
                Opportunity oldCloseDate = Trigger.oldMap.get(o.ID);
                if (o.CloseDate != oldCloseDate.CloseDate) 
                {

                    Integer DayDiff = o.CloseDate.daysBetween(Trigger.oldMap.get(o.ID));

                    Date newDate = o.CloseDate;

                    OpportunitySchedulingHandler.ScheduleDateUpdate(o.id, newDate, DayDiff);
                }
            }

In your class, you need to modify how it handles this. First, your method should be defined to accept lists or maps, not single strings, integers, dates, etc. Your code is already bulkified to handle lists. I recommend you consider changing your DateDiff list to a map so you can link those values to the opp.id and then pass them along with your lists to your method after the end of your for loop all at once.

I think that another part of what's happening is that you're calling the two methods separately and not linking them with the oppId. As such, they're not being updated concurrently. I strongly suspect that's why new entries are being created for dates that are not linked to your existing line items; something which could be related to the fact this is in a Before context. To fix that, I'd recommend putting all of this into an After Trigger. You're not gaining anything by having both before and after triggers that I can see since trigger.new values are not being passed back into Opportunity.

 public static void ScheduleDateUpdate(list<String> oppids, list<date> newDates, list<Integer> DayDiff) 
 {
   List<OpportunityLineItem> idval = [SELECT id FROM OpportunityLineItem WHERE OpportunityId=:oppid];
   List<OpportunityLineItemSchedule> datelist = new List<OpportunityLineItemSchedule>();
   for (Integer i = 0; i < idval.size(); i++)
   {
       datelist = [SELECT ScheduleDate FROM OpportunityLineItemSchedule WHERE OpportunityLineItemId =:idval[i].id];
       for (Integer k = 0; k < datelist.size(); k++)
       {
           //Date mydate = Date.newInstance(datelist[k].ScheduleDate.Year(),datelist[k].ScheduleDate.Month(),datelist[k].ScheduleDate.Day());
           if(DayDiff <> 0)
           {
            Date mydate = date.newInstance(year,month,day);
            datelist[k].ScheduleDate = mydate.addmonths(k);
           }
        }
       update datelist;
   }
 }    

In your method above, your adding months to your scheduledDate by creating a new instance of the scheduled date, then adding the value of the iterator k to the number of months for the new instance. I can't see where this has anything to do with the DayDiff you calculated earlier in your trigger.

This tells me either you don't need to calculate the DayDiff in your trigger, or you need to be using it in the above section of your class to calculate the new ScheduleDate. I'd think the latter would be the case. If so, I'd expect you to take the existing ScheduleDate and use the addDays method to calculate the new date. If you do that, your two queries can be combined into one query and you'll only need a single for loop.

public static void ScheduleDateUpdate(map<Id,Integer> dayDiffMap) 
     {

       List<OpportunityLineItem> updtlst = new list<OpportunityLineItem>();    
       List<OpportunityLineItem> datelist = [SELECT id, ScheduleDate, OpportunityId FROM OpportunityLineItem WHERE OpportunityId IN :dayDiffMap.keyset()];

       For(OpportunityLineItemSchedule oli:datelist)
       {
          d = new OpportunityLineItemSchedule(Id = oli.Id, OpportunityId=oli.Opportunity.Id);
          d.ScheduleDate = oli.ScheduleDate.addDays(dayDiffMap.get(oli.OpportunityID);

          updtlst.add(d);
       }

       update updtlst;
    }

Notice that when you do this, all you need to pass it is the dayDiffMap which contains the oppIds and the Differences in Days. You don't need the new close date or a separate list of Opp Id's.

Edit

Here's some very relevant info from the ObjectReference on OpportunityLineItemScheule:

Additionally:

The Quantity and Revenue fields have the following restrictions when this object is updated:

• For a schedule of Type Quantity, you can’t update a null Revenue value to non-null. Likewise for a schedule of Type Revenue, you can’t update a null Quantity value to non-null.

• You can’t null out the Quantity field for a schedule of Type Quantity. Likewise you can’t null out the Revenue field for a schedule of Type Revenue.

• You can’t null out either the Revenue or Quantity fields for a schedule of type Both.

See the ObjectReference for even more restrictions related to this object as well as OLI...