Reschedule Products - Introduction
Our client provides payment gateway services and wins orders that have scheduled opportunity products over multiple years.
When an opportunity line item is added to an opportunity, monthly scheduled item records are automatically generated starting from the close date of the opportunity.
However, the opportunity also contains a custom field ‘Revenue Start Date’ which should be the first scheduled date of the scheduled line items. As the opportunity progresses through the lifecycle, the client frequently changes the Revenue Start Date and requires that the scheduled line items be automatically re-scheduled from this new date.
Furthermore the requirement is that if the Revenue Start Date is set for the last day of a month, then the subsequent Scheduled Dates must also contain the last day of the month.
We built a Flow definition to satisfy this requirement and this note explains the solution design.
Opportunity Products.
The client has circa 40 products of which 20% are enabled for revenue scheduling. All schedulable products are scheduled at monthly intervals. This simplifies the solution.
Process Flows
The headless flows are launched by Process Builder workflows that are in turn triggered by
- The addition of a new schedule opportunity product line item
- A change in the Revenue Start date
An overview of the flow that creates a schedule based on the Revenue Start Date is shown below:
Flow to reschedule opportunity products
- The flow starts by looking up the Opportunity that needs to be rescheduled, and setting the value of the variable Revenue Start Date
- The Fast Lookup creates a collection variable of all the scheduled opportunity product records, and sorts these in Ascending Start Date order.
- The loop specifies the loop through the RevenueScheduledRecords in ascending order. and assigns an ID and start date variable of each loop record.
- This Assignment element works out the month number of the ScheduleDate of each loop record. (It uses the result of the Assignment element, indexed as 9 in the diagram. This simply counts the number of scheduled records in the loop. )It calculates the Month number of the Revenue Start month, and adds the index number of the scheduled records in the loop. E.g, for the first record in the loop the Index number or record count is 0, hence If the revenue Start date =1/03/2017, then the month number = 3 +0=3.For the say, 5th record in the loop the Month number will be 3+4=7In fact as we are covering scheduled over more than one year, the required formual to work out the month number is as follows.If(MOD(MONTH({!NewRevenueStartDate})+{!varCountSchedItem},12)=0,12, MOD(MONTH({!NewRevenueStartDate})+{!varCountSchedItem},12))
- This Assignment works out the Year of the Scheduled Date for each loop record
YEAR({!NewRevenueStartDate})+FLOOR((MONTH({!NewRevenueStartDate})+{!varCountSchedItem})/12)
- This Assignment works out the relevant DAY of the Scheduled Date for each Scheduled record. The formula logic is convoluted because we need to cater for populating a relevant last day of a month for each scheduled record. I.e if the start date is say 30th of April, the the scheduled records for months that do not comprise 30 days must have a Day number =31.
If(
AND(
DAY({!NewRevenueStartDate})>=30,
OR({!varNextMonthNo}=4,{!varNextMonthNo}=6,{!varNextMonthNo}=9,{!varNextMonthNo}=11)
), 30,
If(
AND
(
DAY({!NewRevenueStartDate})>=28,{!varNextMonthNo}=2, MOD({!varLoopYear},4)=0
), 29,
If(
AND
(
DAY({!NewRevenueStartDate})>=28,{!varNextMonthNo}=2, MOD({!varLoopYear},4)>0
), 28,
If(
AND
(
DAY({!NewRevenueStartDate})=30,
OR
(
MONTH({!NewRevenueStartDate})=4,MONTH({!NewRevenueStartDate})=6,MONTH({!NewRevenueStartDate})=9, MONTH({!NewRevenueStartDate})=11
),
OR
({!varNextMonthNo}<>2 , {!varNextMonthNo}<>4, {!varNextMonthNo}<>6, {!varNextMonthNo}<>9, {!varNextMonthNo}<>11
)
),31,
DAY({!NewRevenueStartDate})
))))
- This assignment, populates the scheduled Date for the current loop record.
The formula is as follows
DATE({!varLoopYear},{!varNextMonthNo}, {!FormLoopDayValue}) - This assignment adds the new Scheduled date to a collection of records which will be used to fast update the scheduled records that have passed through the loop
- This assignment just counts the number of Scheduled records in the loop
- This fast update, updates all scheduled records with the correct sequence of reschedule datesEND OF DOCUMENT