6

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.

4
  • Where are you having a problem at? Also, why are your doing a BeforeInsert and an AfterUpdate? A Before trigger operates very differently than an After trigger. I don't see you taking advantage of operating on Trigger.new in any kind of a "before" section of code. Perhaps you need to write the code separately and then combine the code into one trigger if you truly want a Before Insert and an After Update. – crmprogdev May 26 '15 at 19:20
  • Please refer comment below – Dave Jun 4 '15 at 13:57
  • 1
    Click on edit at bottom left of your post above to update it. Additional info should not be posted as an answer to your question. Please visit the help center center to learn how to use this forum. Suggest you add debug statements to your code and look at the logs. See How do I start to debug my own Apex code?. – crmprogdev Jun 4 '15 at 14:02
  • Please check updated code and comments. – Dave Jun 15 '15 at 13:29
5

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

0
3

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:

OLISchedule Allowed Field Types Table

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...

28
  • Thanks for explaining logic behind two different updates, In your class code, In line#5 you used OpportunityLineItem to fetch ScheduleDate and OpportunityID, where OpportunityLineItem contains OpportunityID but not ScheduleDate, same for OpportunityLineItemSchedule which contains ScheduleDate but not ID, if we try to link them by foreign key, it will throw error of invalid foreign key. same for line #9 where relationship is not valid + need to associate d with List or Integer where it won't allow foreign key use. – Dave Jun 15 '15 at 17:44
  • About my code, I used dayDiff previously where I was adding days as per dayDiff value, in this new code, I'm taking close date integers as a reference and adding months comparing that. If we add days, it will give error for month ending days, ex. if we add 31 Jan then it'll give 3rd Mar, 31 Mar, 1 May, 31 May and so on. for getting that correct, we'll need to add different logic for every month or have to add by month, so I used month here. – Dave Jun 15 '15 at 17:49
  • My bad, I missed that on the query, Was a long reply to your post. You should still be able to do a subquery to capture that in a single query. I'd have to look in Workbook or some other tool to verify, but that's what I'd expect. – crmprogdev Jun 15 '15 at 18:11
  • As for the difference in days, I was simply trying to point out that if both the prev and new close dates were on the 29th of the month, your method wouldn't show a difference in the number of days. Your method for months in your class to create the new scheduleDate, made no sense to me at all. I couldn't see how it was related to the difference between old & new close dates, or the diff value you passed in from the trigger. If this answer leads you to resolving your issues, please help the rest of the community by marking it as having answered your question. – crmprogdev Jun 15 '15 at 18:16
  • Sure, I'm trying to use the logic you provided ( Used it before using list, didn't work, using maps now ). If this leads to the solution, that would be great for me and others too, so I'll update new code here. Meanwhile, I'm updating upgrades with less errors in code time by time and yes, you're right, if I change just month with same date on, it is not making any change in schedule so I'll have to work on that but if we use days, then it just add days in current date which will create problem in scheduling date for month having less number of days than other (scheduled, ex. feb) – Dave Jun 15 '15 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.