Hi Friends,

In our CA PPM journey, we get to pen down so many GEL scripts to work in all those CA PPM workflows. In many of them, we often xog out an object instance, modify some values or add some values to the xogged out xml and xog back the modified xml document to affect a change. One crucial part in this exercise is searching for the right node or element in the xogged out xml document. Me being a big zero in JAVA, I found using the XPath predicates too simple and handy for this purpose. Sharing the same in this blog post. Let's take this ride!

The Story Begins

It all began when I had a new requirement to modify the monthly values in a financial plan via XOG. As I had never used XPath predicates before, the task seemed almost impossible to me at first. Let us look at a sample Benefit Plan xog out below. Imagine trans_class as a custom attribute linked to a transaction class lookup, which we created in the  "Benefit Plan Detail" object.

Now how will you navigate to the first of the three Detail sections you see here and extract one particular numerical value in the above picture? There can be many different ways to do this, like getting the first of these in this array list. But rather than trusting on any order or list, I will choose to use XPath predicates here. Please note that I have stored the xog read output under xogReadOutput variable.

Our Hero - XPath Predicate

If there was only one month segment under the Detail_1 section and if you are OK with filtering on that DetailName field, then something as simple as below will get you the monthly value.

<gel:set asString="true" select="$xogReadOutput//BenefitPlan/Details/Detail[@detailName='Detail_1']/Benefit/segment/@value" var="to_print"/>

The piece of code above highlighted in RED within square brackets is called as Predicate. It filters a node-set by evaluating the predicate expression on each node. As you see here, we filter on the detailName attribute verifying if it has a value of Detail_1.

Nested Predicates

But I don't want to filter by the detailName field as it is a name field. What if I would like to filter through each Detail section above by looking at that custom lookup attribute I had created in the Benefit Plan Detail object - trans_class? Here we can make use of Nested Predicates.

<gel:set asString="true" select="$xogReadOutput///BenefitPlan/Details/Detail/Benefit/segment[../../CustomInformation/ColumnValue[@name='trans_class']/text()='trans_class_1']/@value" var="to_print"/>

As you see above, we have used predicate inside a predicate here. The ../../ allows us to jump from the segment element to its grandparent Detail node and then go into the CustomInformation node. Unlike our screenshot above, trans_class may not be the only custom attribute there, so we filter again using another predicate to go into the trans_class columnValue element and use the text() to retrieve the value of it and validate it against the value we want to filter on, i.e. trans_class_1. 

Multiple Predicates

Now let us come to a real life scenario when you have multiple month segments as in the above screenshot. Here you will need more than one predicate joined together in AND logic. 

<gel:set asString="true" select="$xogReadOutput///BenefitPlan/Details/Detail/Benefit/segment[@start='2018-04-01T00:00:00'][../../CustomInformation/ColumnValue[@name='trans_class']/text()='trans_class_1']/@value" var="to_print"/>

Multiple predicates are written here one after the other and they give us the effect of an AND condition. Only thing to remember here is that the predicate expressions must make sense to the single node/element against which they are applied. Just like here, we mention the first predicate without any path modification as 'start' is an attribute existing on the same segment element but the second predicate is appropriately modified and prefixed to reach the CustomInformation node in the hierarchy.

Predicates in GEL Scripts

Once you are clear until here, I assume now my blog post's header image will make sense to you. Attaching the same image below again. (Please click on it to enlarge it)

As you see, I am getting all the billing changes I need to do in the GEL via some SQL query named as "Billing", each row of which I am storing to "Billing_Changes".In the first gel:set line, I am checking for the existence of some billing values already for the particular transaction class and the particular month in the xogged out Benefit Plan.

The next core:choose code helps me to add the delta value to the existing Billing value and get the new Billing value to replace  OR  if there is no existing Billing value for that month, then just insert the delta value as such against that month along with the full segment element to lie under the Benefit node. Note here, how we use the same predicates again while inserting/replacing some values in the XML, not just for value extraction from the XML.

Some Basics for GEL Beginners

I am not sure if all my above explanation and code will make complete sense to a GEL script beginner. So some more details and GEL script tips just for them. Experts, please ignore 

1. XPath search / and //

You might have noticed usage of both / and // in the above code. There is indeed a difference between the two. / is used when we know the parent element and when we construct the continuous parent-child path. But as in the above example, if you don't want to mention each of the nodes until the node you need and want to quickly jump to a particular node in the XML document, then we use the // search directive.

2. Beware of the two types of gel:set statements

Syntax alert! We use the one with asString="true" to extract some value or information from the XML document specified in the select attribute and store it in the variable we mention in the var attribute. We use the other one with insert="true" to insert/replace some information in the XML document at the location specified in the select attribute,  with the information provided in the value attribute.

3. When to use the @ construct and the text()

In the above piece of code, you might have noticed usage of both @ and text() while extracting data from the XML document. It is simple. When you want to extract data from an XML attribute (like the value attribute in the segment element in our example),  then we use @attribute. On the other hand, if your data lies within an XML element (like the ColumnValue element in our example), then we use the text().

4. Double check for element existence

Whenever you want to modify some information in an object instance and if you do it via the xogout-xogin method, make sure the value you want to replace/modify does exist in the xogged out XML document. It can very well happen that the particular attribute on the object instance is blank/null in which case a XOG READ output of that object instance will not contain the XML element at all. And then imagine you trying to navigate to that xml element assuming the xog out code will have it and trying to set a value inside it --- this will certainly result in an error. That's where the core:choose section in the above screenshot helps. It first checks for the existence of the xml element in the XOG OUT code, does some actions if it really exists there. It will also parse and create an entire XML element to insert into the appropriate node if it does not exist.

5. Never trust only on state="SUCCESS"

This final tip is a word of caution. While there are many GEL scripts I see around which check for each value against failureRecords, insertedRecords, totalNumberOfRecords, updatedRecords etc in the XOGOutput section which we get as a result of a XOG read/write operation, a much bigger number of people are lazy. Me too, sometimes! They tend to check only the state attribute in the Status element and if it shows SUCCESS, they gel:log it saying "hurray...we did it". But I have seen in many cases, especially while working on financial plans, that a SUCCESS state alone may not imply that the new financial plan has been updated or created. There could be reasons like an exchange rate not existing in the system etc. In all those cases, the ErrorInformation node in the XOG output tells us what went wrong. So it is best to add this one more additional line where you extract the ErrorInformation node and store it to display later when an error happens. Also, you could check for it being empty along with our state="SUCCESS" check. Refer the screenshot below.

That's it guys. We come to the conclusion part of this post. Will be back again if I find something tricky or useful. I know, this one was lengthy. But I assume this will be helpful to someone in this community. 

Regards,

Georgy

Hi Friends,

What do you miss the most in CA's online documentation available on CA Clarity PPM? If you ask me, my answer will be database column level details of the CA PPM process tables i.e. the BPM tables. Not sure why CA skipped this section in the Technical Reference Guide when it has almost all other OOTB table columns explained in detail 

Need for Database Schema Details of BPM Process Tables

The complete database schema details of the BPM process tables for CA PPM would have helped us in many different ways. It can be to drill down to the process and its instance whose error is tagged in the PPM log files where they might just refer the process instance id or the thread id. We could have also used the information to do some general global searches across all Clarity processes, their log messages or a search in all GEL scripts. 

Given below are a set of Oracle SQL queries touching most of the BPM process tables. Even if this is not complete, it can be used as a quick reference point to know the table names and their foreign keys to be used. Before running the below SQL queries, please make sure that you are replacing the text in bold red with appropriate values from your CA PPM environment.

Query Connecting BPM_DEF Tables

select 

caption.name process_name,

defn.process_code,

step.step_code,

action.action_code
from
BPM_DEF_PROCESSES defn
inner join BPM_DEF_PROCESS_VERSIONS ver on ver.process_id=defn.id
inner join BPM_DEF_STAGES stg on stg.process_version_id=ver.id
inner join BPM_DEF_STEPS step on step.stage_id=stg.id
left join BPM_DEF_STEP_ACTIONS action on action.step_id=step.id
left join BPM_DEF_STEP_ACTION_PARAMS parm on parm.step_action_id=action.id
left join BPM_DEF_STEP_AI_ACTIONS aiaction on aiaction.step_action_id=action.id
left join BPM_DEF_ASSIGNEES assignee on (assignee.table_name='BPM_DEF_STEP_ACTIONS' and assignee.pk_id=action.id)
left join BPM_DEF_STEP_CONDITIONS cnd on cnd.step_id=step.id
left join BPM_DEF_STEP_TRANSITIONS trnstn on trnstn.step_condition_id=cnd.id
left join CMN_CUSTOM_SCRIPTS scripts on scripts.id=action.script_id
left join CMN_CUSTOM_SCRIPT_PARAMS par on par.script_id=scripts.id
left join BPM_DEF_OBJECTS obj on (obj.pk_id=ver.id and obj.table_name='BPM_DEF_PROCESS_VERSIONS')
inner join CMN_CAPTIONS_NLS caption on (caption.table_name='BPM_DEF_PROCESSES' AND caption.language_code ='en' AND caption.pk_id=defn.id)
where

defn.process_code='<process code>'
group by caption.name, defn.process_code, step.step_code, action.action_code
order by Process_Name, step_code, action_code

NOTE: For a couple of tables like BPM_DEF_ASSIGNEES and BPM_DEF_OBJECTS, multiple join conditions are possible as decided by the table name value specified in the TABLE_NAME column of these tables. Based on that the values provided for TABLE_NAME and PK_ID columns in the query join condition can be tweaked.

Query Connecting BPM_RUN Tables

select
caption.name process_name,
defn.process_code,
runs.id process_instance_id,
runs.status_code,
runs.start_date,
runs.end_date,
csu.first_name||' '||csu.last_name Run_By
from
BPM_RUN_PROCESSES runs
inner join BPM_RUN_PROCESS_ENGINES engine on engine.id=runs.process_engine_id
inner join BPM_RUN_STEPS steps on steps.process_instance_id=runs.id
inner join BPM_RUN_THREADS threads on (threads.process_instance_id=runs.id and steps.thread_id=threads.id)
inner join BPM_RUN_OBJECTS obj on (obj.pk_id=runs.id and obj.table_name='BPM_RUN_PROCESSES')
left join BPM_RUN_STEP_COND_RESULTS condres on condres.step_instance_id=steps.id
left join BPM_RUN_STEP_TRANSITIONS trnstn on trnstn.step_instance_id=steps.id
left join BPM_RUN_STEP_ACTION_RESULTS actres on actres.step_instance_id=steps.id
left join BPM_RUN_ASSIGNEES assignee on (assignee.pk_id=actres.id and assignee.table_name='BPM_RUN_STEP_ACTION_RESULTS')
left join BPM_RUN_ASSIGNEE_NOTES notes on notes.run_assignee_id=assignee.id
inner join BPM_DEF_PROCESS_VERSIONS ver on ver.id=runs.process_version_id
inner join BPM_DEF_PROCESSES defn on defn.id=ver.process_id
inner join CMN_CAPTIONS_NLS caption on (caption.table_name='BPM_DEF_PROCESSES' AND caption.language_code ='en' AND caption.pk_id=defn.id)
left join CMN_SEC_USERS csu on csu.id=runs.created_by
where
defn.process_code='<process code>'
and runs.id=<process instance id>
group by caption.name, defn.process_code, runs.id, runs.status_code, runs.start_date, runs.end_date, csu.first_name||' '||csu.last_name
order by process_name, process_instance_id

NOTE: The above note applies here also, to both the BPM_RUN_ASSIGNEES and BPM_RUN_OBJECTS tables.

Search All Process Log Messages

We usually print some important information to the process log messages using the gel:log tag within GEL scripts. This will insert this data in the BPM_ERRORS table when we execute the script in a custom step within a process inside Clarity. This happens irrespective of the warning level used for this tag in the GEL script. The only thing to note here is that as this is a process message and not a logger one, DEBUG/INFO becomes INFO, WARN is WARNING, and ERROR/FATAL becomes ERROR messages in the BPM_ERRORS table. 

Being the system administrator or the process creator, if we already know the error/warn/info message text that can be expected from different processes, the below search query on all the process messages can quickly give us a list of process instances that need our attention.

select
caption.name process_name,
defn.process_code,
runs.id process_instance_id,
runs.status_code,
runs.start_date,
runs.end_date,
csu.first_name||' '||csu.last_name Run_By,
be.type_code warning_level,
be.exception_trace2 Log_Message,
arg.arg_value error_argument_value
from
BPM_ERRORS be
inner join BPM_RUN_PROCESSES runs on runs.id=be.process_instance_id
inner join BPM_DEF_PROCESS_VERSIONS ver on ver.id=runs.process_version_id
inner join BPM_DEF_PROCESSES defn on defn.id=ver.process_id
inner join CMN_CAPTIONS_NLS caption on (caption.table_name='BPM_DEF_PROCESSES' AND caption.language_code ='en' AND caption.pk_id=defn.id)
left join BPM_ERROR_ARGUMENTS arg on arg.error_id=be.id
left join CMN_SEC_USERS csu on csu.id=runs.created_by
where

UPPER(be.exception_trace2) like UPPER('%your search text%')
order by process_name, process_instance_id

 

Modify any of the above queries as needed for your use.

Happy BPM-Querying 

Regards,

Georgy

Hi Friends,

We use lookups in many places in CA PPM. Sometimes we attach a lookup to a new custom attribute in any object or to a NSQL query attribute so that it becomes available in the Grid portlets made from that NSQL query.

So if you want to modify a lookup, how do you know where all that lookup is currently in use? Or if you want to delete a custom lookup, how will you check which all are the impacted items? The following Oracle SQL queries will help you in this mission.

Object Attributes Using A Particular Lookup

SELECT

lookup_type "Lookup ID",

object_name "Object ID",

column_name "Attribute ID",

data_type "Data Type",

CASE is_active WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END "Active?"

FROM

ODF_CUSTOM_ATTRIBUTES

WHERE

lookup_type='Your Lookup ID'

ORDER BY object_name, column_name

Object Based Portlets Using A Particular Lookup

As you very well know, any portlet created from a Clarity Object can use any of the Object attributes. So the only additional information the below query will give you is the list of all those Object based portlets where the lookup attribute is currently available for use.

SELECT

oca.lookup_type "Lookup ID",

cp.portlet_code "Portlet ID",

oca.object_name "Source Object ID",

oca.column_name "Attribute ID",

oca.data_type "Data Type",

CASE oca.is_active WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END "Active?"

FROM

CMN_PORTLETS cp

INNER JOIN CMN_GRIDS cg ON (cg.portlet_id=cp.id AND cg.principal_type='SYSTEM')

INNER JOIN ODF_CUSTOM_ATTRIBUTES oca ON oca.object_name=cg.dal_code

WHERE

oca.lookup_type='Your Lookup ID'

ORDER BY cp.portlet_code, oca.object_name, oca.column_name

NSQL Query Based Portlets Using A Particular Lookup

SELECT

cnqf.lov "Lookup ID",

cnqf.filter_association "Attribute ID",

cgnq.query_code "Query ID",

cp.portlet_code "Portlet ID",

CASE cp.is_active WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END "Active?"

FROM

CMN_NSQL_QUERIES cnq

INNER JOIN CMN_GG_NSQL_QUERIES cgnq ON cgnq.cmn_nsql_queries_id=cnq.id

INNER JOIN CMN_NSQL_QUERY_FILTERS cnqf ON cnqf.cmn_nsql_queries_id=cnq.id

LEFT OUTER JOIN CMN_GRIDS cg ON (cg.dal_id=cnq.id AND cg.principal_type='SYSTEM')

LEFT OUTER JOIN CMN_PORTLETS cp ON cp.id=cg.portlet_id

WHERE

cnqf.lov='Your Lookup ID'

ORDER BY cgnq.query_code, cnqf.filter_association, cp.portlet_code

I hope all the above SQL queries will help you in solving the lookups-used-where mystery which we are facing right now in CA PPM. Feel free to modify them as per your needs.

Regards,

Georgy

Hi Friends,

Suppose one fine day, you feel like checking the user login history of your Clarity application. Where will you go to get the list of users who had logged into Clarity, along with their login dates and the number of times each user had logged-in? CA PPM, traditionally stores session information in two database tables but each has its own disadvantages.

Traditional Session Tables

• CMN_SESSIONS --- stores information of ACTIVE authenticated user sessions only. Moreover the entries get automatically removed either when the user logs out or when the session expires and is cleared by the session cleanup job.
• CMN_SESSION_AUDITS --- keeps a record of when all authenticated user sessions begin and end. But there is no way to differentiate between a session created by a real user login and a session created by the Clarity background (BG) service on behalf of the user who runs or schedules a job/process.

LOG_SESSIONS: The New Session Table from CA PPM v14.1

This new LOG_SESSIONS table contains the session created date, the session token and the logged-in user id. The same Clarity 14.1 version also introduced other LOG_XXXXX tables too. We only need to set them up properly which is given in detail under "Setting Up The Log Tables" in the article Most Viewed Portlets In Clarity PPM.

NOTE: The only additional step you need to do here is to make sure the parameter 'Session token retention in days' in Delete Log Analysis Data job is set appropriately. As is evident from the parameter name, the Clarity login history details you get when you use the below query is only for the number of days you set for this parameter in this scheduled job. Set both the parameters (Log retention in days and Session token retention in days) in Delete Log Analysis Data job to a higher value if you desire longer login history. This will result in adequate data to be present in the LOG_DETAILS and LOG_SESSIONS tables which are used by the following SQL query.

Advantages of LOG_SESSIONS table

• Entries remain in the table even after the user logs out of Clarity PPM
• By coupling it with the LOG_DETAILS table, we can differentiate whether it's a BG session or a real user session

Clarity User Login History Query

SELECT

csu.user_name,

csu.first_name||' '||csu.last_name Full_Name,

TRUNC(ls.created_date) Date_Of_Login,

COUNT(*) Times_Logged_In

FROM

log_sessions ls,

cmn_sec_users csu

WHERE

csu.id = ls.user_id

AND ls.token IN (SELECT distinct session_cookie FROM log_details) ----- To exclude all the BG sessions

GROUP BY csu.user_name, csu.first_name||' '||csu.last_name, TRUNC(ls.created_date)

ORDER BY TRUNC(ls.created_date) DESC, COUNT(*) DESC

Feel free to edit the above Oracle query as per your needs. A grid portlet with the above result fields can be easily made by changing this SQL query to NSQL.

NB: The most important task here is to setup the log tables by properly scheduling both the jobs i.e. Tomcat access log import/analyze job and Delete Log Analysis Data job. Their job schedule and parameter values determine how much login history is available to us.

Regards,

Georgy

Hi Friends,

As with the my previous blog post on creating a filter portlet, this is another area where we did not have any requirement from clients and which was never explored. This blog post is about creating those Graph or Chart portlets in Clarity which now look kind of beautiful to me (no, these are not the Xcelsius interactive ones).

I have used a very basic NSQL query to provide data to these portlets. The query just gives ETC per resource in a project.

Click on the permalink at the bottom of this post to see the attached word document.

Pie Chart

Donut Chart

Funnel Chart

Bar Chart

Line Chart

Column Chart

Area Chart

Happy claritying guys,

Georgy N Joseph