Solving impossible date & time calculations with #BRFplus and a Factory Calendar

[Jocelyn_Dart]

This time of year, Daylight saving is switching on and off around the world, Easter long weekends are giving us a well-earned break, and it’s turning my thoughts to the joys of calendar calculations in business rules.

The word 'time' is the most commonly used noun in English.  Quite Interesting Facts http://qi.com/infocloud/time

Image courtesy of stockimages / FreeDigitalPhotos.net

As much as I love http://www.timeanddate.com (and working in a global company sometimes it feels like I live and breathe by their Timezone converter) there are some date and time questions it just can’t answer.  Never mind differing timezones and the added joys of daylight saving, with so many different countries, and so many different regions (states/counties/cantons) within countries, just working out which days are public holidays at a site is a serious software challenge.  Add to that the differing working weeks which can vary from site to site, plant to plant, location to location, even from oil rig to oil rig.  You might work in a traditional Monday to Friday 9am to 5pm office, but that oil rig or manufacturing plant – they may be working a 24x7 week, a 18x6 week or any other variation thereof.

It’s not just about knowing if the office/site is going to be open on a certain date/time, knowing if a particular day is counted as a regular working day or as a public holiday can be critical to paying the correct penalty rates for staff on the payroll, and that can lead to all sorts of #nofunatall with workers, industrial unions, and ever delightful government bodies such as Taxation authorities.

Workers penalised over public holiday confusion - ABC Gippsland Vic - Australian Broadcasting Corporation http://www.abc.net.au/local/stories/2010/12/07/3086983.htm

Having a way to handle these date and time variations is a critical challenge to business rules involving dates, such as determining if a service has been performed within the Service Level Agreement, or whether sales dollars can be counted against the current sales quarter, or when the next delivery must fall if it is to reach a factory site during open hours.

The good news is SAP solved this problem many years ago… by introducing the Factory Calendar.  But did you know you can also use the Factory Calendar in your SAP NetWeaver Decision Service Management and BRFplus business rules today?

Factory calendar maintenance in SAP http://www.learnsaptips.com/2012/08/factory-calendar-maintanance-in-sap.html

The Factory calendar was introduced literally decades ago… I’ve found references as far back as 1999 Release R/3 3.0c. It is built into so many applications across SAP it barely gets noticed anymore it’s so much a part of the virtual SAP furniture.  Those of us in the workflow space use it regularly for working out deadlines based on working days.  You can even schedule background jobs based on the factory calendar!

Schedule a background job using a factory calendar! http://****************/Tips/ABAP/BackgroundJob/FactoryCalendar.htm

So how exactly do you take advantage of factory calendars in business rules? Here’s how…

Prerequisites

1. Create (or maintain) your factory calendar in Transaction SCAL. SAP delivers a bunch of factory calendars but you may want to create your own or at least maintain the public holidays relevant for this year (some public holidays are assigned by government decree at the start of the year).
2. Create a ABAP Class that implements the interface IF_FDT_APPLICATION_SETTINGS and implement the method GET_CALENDARto return the 2 character factory calendar id you want to use, e.g.

   ev_fcalid = 'AU'. "The list of factory calendar ids can be found in table TFACD

3. In the CLASS_CONSTRUCTORmethod of the same class, add the following line that will tell BRFplus you want to set the calendar:

   if_fdt_application_settings~gv_get_calendar = abap_true.

4. Assign that class to your BRFplus rules application as an application exit class.  How to do that is already described here http://scn.sap.com/docs/DOC-4564 - BRFplus Application Exits
5. Link the application exit class in the Properties tab of your BRFplus application
6. Create a BRFplus function in your Rules Application and use a formula expression anywhere within it.

That’s it .... now all your date and time calculations in this Rules Application apply the factory calendar.

Just one more thing to keep in mind… both Factory Calendars and Timepoints assign the days of the week to a number from 0 to 6 as follows:

Saturday = 0, Sunday = 1, Monday = 2, .... Friday = 6

Now I could show you some boring old real life formulas – and we’ll start with a few, but hey this is dates and times so let’s have bit of fun with showing how some of the date and time functions work.  Most of these examples are for dates, but similar built-in formula functions exist for times. And we won’t limit it to just factory calendars…

Find out if the office is open on a certain date

Office Is Open = DT_IS_ACTIVE( My Date)

If the office is closed, find out when it is next going to be open

Next Office Open Date = DT_NEXT_ACTIVE( My Date)

Calculate a deadline based on working days

Deadline Date = DT_ADD_DAYS( Starting Date, Duration In Days)

Work out if it's TGIF (Thank God Its Friday!)

It's Friday = IF ((DT_GET_DAY_OF_WEEK( My Date) EQ Friday) true, false)

Here Friday is a Constant with value 6

How many working hours left to complete that project task?

Working Hours to Complete Task = DT_DURATION_DIFF_HOURS( Starting Date & Time, Completion Date & Time)

How many working days was I on leave

Leave Days Taken = DT_DURATION_DIFF_DAYS( First Day of Leave, Last Day of Leave)

A common problem for meetings ... which timezone is my regional manager in?

Regional Manager Timezone Text = DT_GET_USER_TIMEZONE_TEXT( Language )

Count down the working weeks until the next SAP d-code conference

You could of course set up the SAP d-code date as a Constant expression

Weeks Until Conference = DT_DURATION_DIFF_WEEKS( Today, 20 October 2014 )

One for the time-poor ...Desperately checking for one more week this year

Is a Long Year = DT_HAS_53_WEEKS( Today )

One for the romantics ...Checking for a leap year

Proposal Date = DT_IS_LEAP_YEAR( DT_SET_PART_DAYS( DT_GET_CURRENT_DT( ), '01' ) )

Taking it to the next level

The previous example shows how although we can use date and time calculations one at a time, but because these are BRFplus formulas we can also combine different formulas to solve more interesting challenges.

So when is the last possible working date to transport my configuration to the testing environment?

Testing starts on the 1st of next month and it takes 2 days to go through transport approvals

Last Transport Date = DT_SUBTRACT_DAYS( DT_SET_PART_DAYS( DT_ADD_MONTHS( DT_GET_CURRENT_DT( ), 1 ), '01' ), Duration In Days )

Find the first working day of this week

Remember the first day of the week is Sunday in the Factory Calendar

First Working Day This Week = DT_GET_NEXT( DT_ROUND_TO_FIRST( My Date, 'WEE'  ) )

Find the last delivery date of this month

Last Delivery Day This Month = DT_GET_PREVIOUS( DT_ROUND_TO_LAST( My Date, 'MON' ) )

Find the last selling date of next quarter

Last Selling Date This Quarter = DT_GET_PREVIOUS( DT_ROUND_TO_LAST( DT_ADD_QUARTERS( DT_GET_CURRENT_DT, 1 ), 'QUA' ) )

Which is earlier? My colleague's timezone ahead of the system timezone?

Earliest Date & Time = DT_MIN( DT_GET_USER_TIME( ), DT_GET_SYSTEM_TIME( ) )

Using multiple factory calendars

Now if you need to use multiple factory calendars or dynamically pass a calendar, don't forget you can also create your own custom formula functions. You'll find instructions here http://scn.sap.com/docs/DOC-4582 - Howto create custom BRFplus formula functions.  How hard is it to create your own custom formula function? I put a custom formula together yesterday, after a little bit of research.  It took me about an hour - now not every calculation will be that easy, but once you have worked out your logic it's really not that hard.

There are over 90 built-in Date and Time formula functions in BRFplus - with those plus the use of a factory calendar, there is plenty of scope for solving the most impossible-looking date and time calculations.  So rest easy and enjoy your those public holidays!