A Look into the AE Crystal Ball

2017 is coming and I am really curious about what the year will bring. And of course I am especially interested in what will happen with Automic and CA.

While we don’t know what the future will bring and have to wait, the Automic Engine is able to perform a look into the crystal ball and give us some information about the future.

In one of my last blog articles (How to find skipped jobs), I have already spoken about Calendars in Automic. Today, again, the calendars are in focus. I will show you how you can use the Automation Engine to look into the distant future. Not with magic, but with Calendars and Calendar Keywords – or Calendar Events as they are called in V12.

This blog article is a new edition of the old article “Forsight”, mixed with some info from another old article: “You see for(ward)”.

CALE is Not a Superfood

Kale is on everyone’s lips – even in the literal sense. The vegetable is gaining unprecedented popularity in the wake of superfood fashion.

For us Automic User, however, Cale has a completely different meaning: Cale is the usual abbreviation for calendar in the Automation Engine. Calendars can be used for the timed activation of objects in Automic. The following table shows some object types and the associated Calendars and Calendar Events:

How CALE Works

I have already described it in the above-mentioned article about skipped jobs: The Automation Engine calculates Calendars in advance. When you create a new Calendar Event and save the Calendar, the AE calculates for a certain number of years (set in UC_CLIENT_SETTINGS) when the Calendar Event applies.

For example, if you have a Calendar object “CALE_GENERAL” with the Calendar Event “MONDAYS” on the mandate 1, you can use the following query to determine all the calculated days for the year 2017:

--select the calculated days from OKD
select OKD_Content from OKD
--the Calendar name is in OH, the Keyword name in OKB
inner join OH on OKD_OH_IDNR = OH_IDNR
inner join OKB on OKD_OH_IDNR = OKB_OH_IDNR and OKD_OKB_LNR = OKB_LNR
--filter the client, Calendar name, Keyword name and year
where OH_CLIENT = 1
and OH_NAME = 'CALE_GENERAL'
and OKB_NAME = 'MONDAYS'
and OKD_YEAR = 2017

The result of this query is the content of the corresponding field OKD_Content. This field contains a bit array with 372 characters, 31 characters for each month. Each character represents a day and has a value of 1 or a value of 0, depending on whether or not the condition applies to the day.

To make the boundary between the months easier to calculate, 31 characters are stored for each month. March 1, for example, is always stored in the 63th place, even if we have a leap year.

In 2017, January 1 is a Sunday, the first Monday of the year is January 2. The result of the above query looks like this:

01000000100000010000001000000100000010000001000000100000010000000001…

The gray-marked zeros are nonexistent days 29, 30 and 31 in February.

Calculate Calendar Events for a Day

Now starts the announced look into the future. Use the following script to find out which Calendar Events contain a specific tag. In the example I look for Christmas 2018.

--MS SQL Server
--output the Client, the Calendar and the Keyword
select OH_Client as AE_Client, OH_Name as CALE_Name, OKB_Name as CALE_Keyword_Name
from OH inner join OKB on OH_Idnr = OKB_OH_Idnr
inner join OKD on OH_Idnr = OKD_OH_Idnr and OKB_Lnr = OKD_OKB_Lnr
where OH_DELETEFLAG = 0 and OH_REFIDNR = 0
--filter year 2018, month 12 and day 24
and OKD_Year = 2018
and SUBSTRING(OKD_Content,((12-1)*31+24),1) = 1  --T-SQL
--and SUBSTR(OKD_Content,((12-1)*31+24),1) = 1  --Oracle
--sorting
order by OH_Client, OH_Name, OKB_Name;

Use Case: What‘s Going on in the Future?

We can now use the script from the last section to find out which active JSCH tasks, EVNT, and period containers will run on a particular day in the future. That means, they use a Calendar Event which contains the day.

The query contains ONLY objects with Calendar Events, so it does not return a complete list of tasks that are scheduled for December 24, 2018.

--MS SQL
--PART I
--Retrieve all relevant Calendars and Keywords as "CALEKEYS"
with CALEKEYS (CALEKEYS_Client, CALEKEYS_Calename, CALEKEYS_Keyname) as (
select OH_Client, OH_Name, OKB_Name
from OH inner join OKB on OH_Idnr = OKB_OH_Idnr
inner join OKD on OH_Idnr = OKD_OH_Idnr and OKB_Lnr = OKD_OKB_Lnr
where OH_DELETEFLAG = 0 and OH_REFIDNR = 0
--filter year 2018, month 12 and day 24
and OKD_Year = 2018
and SUBSTRING(OKD_Content,((12-1)*31+24),1) = 1  --T-SQL
--and SUBSTR(OKD_Content,((12-1)*31+24),1) = 1  --Oracle
)
--PART II
--Usage of Calendar Keywords in Schedule-Tasks
select 'JSCH' "Usage Type",
EH_Client "UC4 Client", EH_Name "Object Name", EJPP_OBJECT "Task Name",
CALEKEYS_Calename "Calendar Name", CALEKEYS_Keyname "Keyword Name"
from EH
inner join EJPP on EH_AH_Idnr = EJPP_AH_IDNR
inner join EJPPC on EH_AH_Idnr = EJPPC_AH_IDNR and EJPP_LNR = EJPPC_EJPP_LNR
inner join CALEKEYS on EH_Client = CALEKEYS_Client and EJPPC_CALENAME = CALEKEYS_Calename
and EJPPC_CALEKEYNAME = CALEKEYS_Keyname
--filter only tasks with "Active" flag on
where EJPP_ACTIVE = 1
--filter only "if one condition matches"
and EJPP_CCTYPE = 2
UNION
--PART III
--Usage of Calendar Keywords in Events
select 'EVNT' "Usage Type",
EH_Client "UC4 Client", EH_Name "Object Name", '-' "Task Name",
CALEKEYS_Calename "Calendar Name", CALEKEYS_Keyname "Keyword Name"
from EH
inner join EVP on EH_AH_Idnr = EVP_AH_IDNR
inner join CALEKEYS on EH_Client = CALEKEYS_Client and EVP_CALENAME = CALEKEYS_Calename
and EVP_CALEKEYNAME = CALEKEYS_Keyname
UNION
--PART IV
--Usage of Calendar Keywords in "Recurring Tasks" (C_PERIODs)
select 'C_PERIOD' "Usage Type",
EH_Client "UC4 Client", EH_Name "Object Name", '-' "Task Name",
CALEKEYS_Calename "Calendar Name", CALEKEYS_Keyname "Keyword Name"
from EH
inner join EPD on EH_AH_Idnr = EPD_AH_IDNR
inner join EPDC on EH_AH_Idnr = EPDC_AH_IDNR and EPD_LNR = EPDC_EPD_LNR
inner join CALEKEYS on EH_Client = CALEKEYS_Client and EPDC_CALENAME = CALEKEYS_Calename
and EPDC_CALEKEYNAME = CALEKEYS_Keyname
--filter only if Calendar is used and "if one condition matches"
and EPD_DaysMode = 'C'
and EPD_CALETYPE = 2
--PARTV
--Sorting
order by "UC4 Client", "Usage Type", "Object Name"

Have a Great New Year

This year was incredible in many respects – and the coming one promises to get pretty interesting, too.

I hope that you have an amazing new year ahead of you!