I am in need of some help,
this request has managed to stump Salesforce Developer help desk so thought id ask here.

I have a Subscription from Zuora that has a Start Date.
since we do not Prorate our deals our Activation date for service on later deals need to align to the same day of the month as when the subscription was origionaly started.

I want to create a VR to stop reps from entering an incorrect "account activation date" on their opps (based on matching day to subscription start date) this big issue is a lack of an 'End of month' function in Salesforce to account for situations like subscription starting on a day that does not exist in the current month.

Example:
Subscription start date - 12-31-14
Upgrade OpportunityActivation Date - 2-28-15

It works fine for days 1-28 of any month but as you can see I need to have the VR be able to handle the fact that sometimes the subscription start date and the current month do not have the same number of days and that if the subscription was started at teh end of the month then the activation date should also be the end of the month.


Below is the data Salesforce Support build befor they gave up. any ideas?

Feild: Account Activation Date Standard
This field shows the normal Account Activation Date date for the month and year of the Account Activation Date field and the day of month of the Subscription Start Date field on the related Subscription record

IF( ISBLANK( Contract_Start_Date__c ) , 
NULL , 

DATE( 
YEAR( Contract_Start_Date__c ) , 
MONTH( Contract_Start_Date__c ) , 

IF( DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 
<= 
CASE( MONTH( Contract_Start_Date__c ) , 
2, IF( MOD( YEAR( Contract_Start_Date__c ) , 4 ) > 0 , 28 , 29 ) , 
4, 30 , 
6, 30 , 
9, 30 , 
11, 30 , 
31 ) , 

/* Use the day of month from the Subscription date */ 
DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) , 

/* Use the maximum day of month from the Opportunity date */ 
CASE( MONTH( Contract_Start_Date__c ) , 
2, IF( MOD( YEAR( Contract_Start_Date__c ) , 4 ) > 0 , 28 , 29 ) , 
4, 30 , 
6, 30 , 
9, 30 , 
11, 30 , 
31 ) 
)))


VR: Billing_Period_and_Activation_Days_2 
VR to keep billing period day in Z and opportunity Account Activation Dates in alignment.

/* WIP */ 


/* Check if the Opportunity has a (parent) Simply Measured Plan */ 
! ISBLANK( Simply_Measured_Plan__c ) 

/* Check if the Opportunity has a (grandparent) Zuora Subscription */ 
&& ! ISBLANK( Simply_Measured_Plan__r.Zuora_Subscription__c ) 

/* Check if there is a Subscription Start Date */ 
&& ! ISBLANK( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 

/* Check if days don't match and Subscription date < Oppty Date */ 
&& DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 
<> DAY( Contract_Start_Date__c ) 

&& DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 
<= 
CASE( MONTH( Contract_Start_Date__c ) , 
1, 31 , 
2, 
IF( MOD( YEAR( Contract_Start_Date__c ) , 4 ) = 0 , 
29 , 
28 ) , 
3, 31 , 
4, 30 , 
5, 31 , 
6, 30 , 
7, 31 , 
8, 31 , 
9, 30 , 
10, 31 , 
11, 30 , 
12, 31 , 
0 ) 

&& DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 
< 
CASE( MONTH( Contract_Start_Date__c ) , 
1, 31 , 
2, 
IF( MOD( YEAR( Contract_Start_Date__c ) , 4 ) > 0 , 
28 , 20 ) , 
3, 31 , 
4, 30 , 
5, 31 , 
6, 30 , 
7, 31 , 
8, 31 , 
9, 30 , 
10, 31 , 
11, 30 , 
12, 31 , 
0 )




 

I am in need of some help,
this request has managed to stump Salesforce Developer help desk so thought id ask here.

I have a Subscription from Zuora that has a Start Date.
since we do not Prorate our deals our Activation date for service on later deals need to align to the same day of the month as when the subscription was origionaly started.

I want to create a VR to stop reps from entering an incorrect "account activation date" on their opps (based on matching day to subscription start date) this big issue is a lack of an 'End of month' function in Salesforce to account for situations like subscription starting on a day that does not exist in the current month.

Example:
Subscription start date - 12-31-14
Upgrade OpportunityActivation Date - 2-28-15

It works fine for days 1-28 of any month but as you can see I need to have the VR be able to handle the fact that sometimes the subscription start date and the current month do not have the same number of days and that if the subscription was started at teh end of the month then the activation date should also be the end of the month.


Below is the data Salesforce Support build befor they gave up. any ideas?

Feild: Account Activation Date Standard
This field shows the normal Account Activation Date date for the month and year of the Account Activation Date field and the day of month of the Subscription Start Date field on the related Subscription record

IF( ISBLANK( Contract_Start_Date__c ) , 
NULL , 

DATE( 
YEAR( Contract_Start_Date__c ) , 
MONTH( Contract_Start_Date__c ) , 

IF( DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 
<= 
CASE( MONTH( Contract_Start_Date__c ) , 
2, IF( MOD( YEAR( Contract_Start_Date__c ) , 4 ) > 0 , 28 , 29 ) , 
4, 30 , 
6, 30 , 
9, 30 , 
11, 30 , 
31 ) , 

/* Use the day of month from the Subscription date */ 
DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) , 

/* Use the maximum day of month from the Opportunity date */ 
CASE( MONTH( Contract_Start_Date__c ) , 
2, IF( MOD( YEAR( Contract_Start_Date__c ) , 4 ) > 0 , 28 , 29 ) , 
4, 30 , 
6, 30 , 
9, 30 , 
11, 30 , 
31 ) 
)))


VR: Billing_Period_and_Activation_Days_2 
VR to keep billing period day in Z and opportunity Account Activation Dates in alignment.

/* WIP */ 


/* Check if the Opportunity has a (parent) Simply Measured Plan */ 
! ISBLANK( Simply_Measured_Plan__c ) 

/* Check if the Opportunity has a (grandparent) Zuora Subscription */ 
&& ! ISBLANK( Simply_Measured_Plan__r.Zuora_Subscription__c ) 

/* Check if there is a Subscription Start Date */ 
&& ! ISBLANK( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 

/* Check if days don't match and Subscription date < Oppty Date */ 
&& DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 
<> DAY( Contract_Start_Date__c ) 

&& DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 
<= 
CASE( MONTH( Contract_Start_Date__c ) , 
1, 31 , 
2, 
IF( MOD( YEAR( Contract_Start_Date__c ) , 4 ) = 0 , 
29 , 
28 ) , 
3, 31 , 
4, 30 , 
5, 31 , 
6, 30 , 
7, 31 , 
8, 31 , 
9, 30 , 
10, 31 , 
11, 30 , 
12, 31 , 
0 ) 

&& DAY( Simply_Measured_Plan__r.Zuora_Subscription__r.Zuora__SubscriptionStartDate__c ) 
< 
CASE( MONTH( Contract_Start_Date__c ) , 
1, 31 , 
2, 
IF( MOD( YEAR( Contract_Start_Date__c ) , 4 ) > 0 , 
28 , 20 ) , 
3, 31 , 
4, 30 , 
5, 31 , 
6, 30 , 
7, 31 , 
8, 31 , 
9, 30 , 
10, 31 , 
11, 30 , 
12, 31 , 
0 )