Challange - FIRST_DAY_OF_CURRENT_MONTH - LAST_DAY_OF_CURRENT

weblike

Hi,

I have one app where I want to implement a subscription system like this:
-customers has their subscriptions which are billed in a specific day of the month,
- the billing cycle is monthly

- the billing period can be: Previous month, Current Month, Next Month

What I want to accomplish:
IF the billing day is the 15th day of the month than:
---FOR billed_period = Previous Month ------>the result should be Begin_of_billed_period=01.08.2014 -----------End_of_billed_period=31.08.2014 (i took as example 15.09.2014)
---FOR billed_period = Current Month ------> the result should be Begin_of_billed_period=01.09.2014 -----------End_of_billed_period=30.09.2014)
---FOR billed_period = Next Month ------> the result should be Begin_of_billed_period=01.10.2014 -----------End_of_billed_period=31.10.2014)

For this I added this in attribute in BO:
Begin_of_billed_period
End_of_billed_period
Attr_Billing_Cycle (with option: Monthly)
Billed_Month (with options: Previous Month, Current Month, Next Month)

the rule for Previous month is like this:

BO.End_of_Billed_period=DATE(LAST_DAY_OF_PREV_MONTH(CURRENT_DATE),MONTH(MONT_ADD(CURRENT_DATE,-1)),YEAR(MONTH_ADD(CURRENT_DATE,-1)))

the rule for current month is like this:

If Bo.Attr_Billing_Cycle='Monthly' AND BO.Billed_Month='Current Month' Then
BO.Begin_of_Billed_period=DATE(FIRST_DAY_OF_CURRENT_MONTH(CURRENT_DATE),MONTH(MONT_ADD(CURRENT_DATE,-1)),YEAR(MONTH_ADD(CURRENT_DATE,-1)))

BO.End_of_Billed_period=DATE(LAST_DAY_OF_CURRENT_MONTH(CURRENT_DATE),MONTH(MONT_ADD(CURRENT_DATE,-1)),YEAR(MONTH_ADD(CURRENT_DATE,-1)))

the rule for next month is like this:

If Bo.Attr_Billing_Cycle='Monthly' AND BO.Billed_Month='Next Month' Then
BO.Begin_of_Billed_period=DATE(FIRST_DAY_OF_NEXT_MONTH(CURRENT_DATE),MONTH(MONT_ADD(CURRENT_DATE,-1)),YEAR(MONTH_ADD(CURRENT_DATE,-1)))

BO.End_of_Billed_period=DATE(LAST_DAY_OF_NEXT_MONTH(CURRENT_DATE),MONTH(MONT_ADD(CURRENT_DATE,-1)),YEAR(MONTH_ADD(CURRENT_DATE,-1)))

These rules are not working because nothing is happening. Maybe my logic is wrong.
If someone developed such a system, please give me some advice,
Any help will be greatly appreciated.

Thank you [/b]

customaware

Bill,

Haven't looked to deeply yet but did you cut and paste these Actions?

If so you ave a typo in the action....

In every instance you have

MONTH(MONT_ADD(CURRENT_DATE,-1))

instead of

MONTH(MONTH_ADD(CURRENT_DATE,-1))

Other than that they look ok.

customaware

Whoops!

After reading your post I was subliminally convinced your name was Bill. 😉

weblike

Hi Mark,

thx for the reply...No problem with the name...🙂 ...Now I have added in my signature for no confusion next time...🙂

Yes you are right is a typo in that rule, but is from the copy/paste thing...
In bsv only in one place I have had this typo...I have corrected it, but now I get the following error message in browser:

com.bas.shared.ruleparser.ParseException Unsupported function MONTH

...in logging viewer for the rules I see this:
Exception while calculating the action Subscription.NextDueDateTest=DATE(DAY_OF_MONTH(Subscription.BillingMonthDay),MONTH(CURRENT_DATE,1),YEAR(CURRENT_DATE,1))

I want to calculate when will be the next due date of the subscription...

weblike

Sorry,

My mistake...I corrected this function...no more error, but still no result..:

the correction was:
Subscription.NextDueDateTest=DATE(DAY_OF_MONTH(Subscription.BillingMonthDay),MONTH(MONTH_ADD(CURRENT_DATE,1)),YEAR(MONTH_ADD(CURRENT_DATE,1))).

As I said I want to calculate the next due date....
Why is not returning a result?? 🙁

customaware

Have you checked the log George?

What does it say when it hits this action?

weblike

Yes, I use a lot the logging function...is very helpfull, but in this case I do not know what the message means...
in log viewer the message is:

Executing action Subscription.NextDueDateTest=DATE(DAY_OF_MONTH(Subscription.BillingMonthDay),MONTH(MONTH_ADD(CURRENT_DATE,1)),YEAR(MONTH_ADD(CURRENT_DATE,1))) from rules of object Subscription
Value was not changed because it was equal to the old value NextDueDateTest=null

customaware

George,

in your action.....

Subscription.NextDueDateTest=DATE(DAY_OF_MONTH(Subscription.BillingMonthDay),MONTH(MONTH_ADD(CURRENT_DATE,1)),YEAR(MONTH_ADD(CURRENT_DATE,1)))

Subscription.BillingMonthDay is a number which is the day of the month not a date I suspect.

So.... try....

Subscription.NextDueDateTest=DATE(Subscription.BillingMonthDay,MONTH(MONTH_ADD(CURRENT_DATE,1)),YEAR(MONTH_ADD(CURRENT_DATE,1)))

weblike

Hi Mark,

Thx... you have an "eagle" eye 🙂...This works for calculating the Next Due Date...

Can you advice why other functions do not calculates the begining and ending of the billing period?

If Bo.Attr_Billing_Cycle='Monthly' AND BO.Billed_Month='Previous Month' Then
BO.Begin_of_Billed_period=DATE(FIRST_DAY_OF_PREV_MONTH(CURRENT_DATE),MONTH(MONT_ADD(CURRENT_DATE,-1)),YEAR(MONTH_ADD(CURRENT_DATE,-1)))
BO.End_of_Billed_period=DATE(LAST_DAY_OF_PREV_MONTH(CURRENT_DATE),MONTH(MONT_ADD(CURRENT_DATE,-1)),YEAR(MONTH_ADD(CURRENT_DATE,-1)))

customaware

MONTH_ADD not MONT_ADD?

customaware

MONTH_ADD not MONT_ADD?

weblike

Sorry,

I have pasted above code, but in app is no typo...

In app I replaced CURRENT_DATE with the attribute Next due Date and the rule looks like this:

Subscription.SubscriptionFirstDay=DATE(FIRST_DAY_OF_PREV_MONTH(Subscription.NextDueDateTest),MONTH(MONTH_ADD(Subscription.NextDueDateTest),-1),YEAR(MONTH_ADD(Subscription.NextDueDateTest),-1))

the attribute NextDueDateTest is type DATE. So the above rule for date 09/30/2014 should return : 08/01/2014 ??correct?

I get this in log viewer:

-Exception while calculating the action Subscription.SubscriptionFirstDay=DATE(FIRST_DAY_OF_PREV_MONTH(Subscription.NextDueDateTest),MONTH(MONTH_ADD(Subscription.NextDueDateTest),-1),YEAR(MONTH_ADD(Subscription.NextDueDateTest),-1))

customaware

Haven't been able to test it George but I can see you have some errors in the
action....

Your...
Subscription.SubscriptionFirstDay=DATE(FIRST_DAY_OF_PREV_MONTH(Subscription.NextDueDateTest),MONTH(MONTH_ADD(Subscription.NextDueDateTest),-1),YEAR(MONTH_ADD(Subscription.NextDueDateTest),-1))

Is incorrect for the params of MONTH_ADD in both instances. Bracket must encapsulate both parameters.

As you have it, the -1 is viewed as a parameter for MONTH, not MONTH_ADD

Should be Like.....

Subscription.SubscriptionFirstDay=DATE(FIRST_DAY_OF_PREV_MONTH(Subscription.NextDueDateTest),MONTH(MONTH_ADD(Subscription.NextDueDateTest,-1)),YEAR(MONTH_ADD(Subscription.NextDueDateTest,-1)))

weblike

You are right Mark, my mistake.... I'm not so familiar yet with these function MONTH_ADD...

I have changed the syntax with this, but no result...I smell that I'm close, but something is missing...

the rule now looks like this:
Subscription.SubscriptionFirstDay=DATE(FIRST_DAY_OF_PREV_MONTH(Subscription.NextDueDateTest),MONTH(MONTH_ADD(Subscription.NextDueDateTest,-1)),YEAR( MONTH_ADD(Subscription.NextDueDateTest,-1)))

In log viewer I get this message:
Value was not changed because it was equal to the old value SubscriptionFirstDay=null

customaware

George,

The problem is FIRST_DAY_OF_PREVIOUS_MONTH returns a Date not a day number. Hence you cannot use it to construct a new date as you are trying to do.

I would expect the first day number of a previous Month to be always 1.

Hence you should use

Subscription.SubscriptionFirstDay=DATE(1,MONTH(MONTH_ADD(Subscription.NextDueDateTest,-1)),YEAR( MONTH_ADD(Subscription.NextDueDateTest,-1)))

weblike

thx, it makes sense ...I will try this...

For beginning of the month is simple..first day is always 1 🙂...

The last day is the problem

Question:

It is possible to use FIRST_DAY_PREV_MONTH to extract only the day like this:

Subscription.SubscriptionFirstDay=DATE(DAY(FIRST_DAY_OF_PREV_MONTH(Subscription.NextDueDateTest)),MONTH(MONTH_ADD(Subscription.NextDueDateTest,-1)),YEAR( MONTH_ADD(Subscription.NextDueDateTest,-1)))

customaware

Try using....

DAY_OF_MONTH(LAST_DAY_OF_PREV_MONTH(somedate))

customaware

How did you go George?

Did it work?

weblike

Hi Mark,

Excuse me...I was out...

Yes, I have tried it last night and it works great..
Now my vision about these functions are more clear..

Thanks again for your help..I appreciate it more than you think 🙂

Have a great day Mark!!!