Getting Started with GEL Scripts in Clarity Educational Community
Explore the power of GEL scripting in Clarity Educational Community with sessions on script exercises, prerequisites, automation benefits, and data sources. Understand what GEL is, its execution language, and how it enables custom logic in CA PPM. Dive into GEL basics, automation advantages, and script structure using SOAP, JDBC, file systems, and web services.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
www.regouniversity.com Clarity Educational Community Getting Started with GEL Scripts Updates and Automation Presented by : Virginia DeCeglia & Chris Shaffer
Agenda Overview Introducing GEL Scripting Scripting Exercises Q&A 2 Clarity Educational Community
Prerequisites Basic knowledge creating CA PPM processes Oracle/SQL language CA PPM data model 3 Clarity Educational Community
Power of Automation Loses Runs script Writes script to automate Time Spent Wins Gets annoyed Does it manually Makes fun of geek s complicated method Does it manually Task Size 4 Clarity Educational Community
What is GEL? GEL stands for Generic Execution Language Based on Jelly, a jakarta.apache.org Commons project Extended and embedded into CA PPM to enable custom logic to solve business problems GEL is the basis for the enterprise application integration framework within CA PPM GEL scripts can be executed from within CA PPM by installing them as processes, or from the command line GEL scripts are executed top down 5 Clarity Educational Community
What is GEL?, cont. Additional information can be found in the CA Documentation (CAClarityPPM_XOG_DeveloperGuide_ENU.pdf) and at the Apache Jelly website at http://jakarta.apache.org/commons/jelly/index.html. 6 Clarity Educational Community
GEL Data Sources Web Services GEL can read or write to any SOAP based web service including XOG File System GEL can read or write to any delimited file accessible on the application server FTP GEL can upload or download to FTP servers JDBC GEL uses JDBC to read and write to databases 7 Clarity Educational Community
GEL Script Structure <?xml version="1.0" encoding="UTF-8"?> <gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:core="jelly:core" xmlns:gel="jelly.com.niku.union.gel.GELTagLibrary" xmlns:file="jelly.com.niku.union.gel.FileTagLibrary" xmlns:soap=" jelly.com.niku.union.gel.SOAPTagLibrary" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly.sql" xmlns:xog="http://www.niku.com/xog" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance"> <!-- Code goes here --> </gel:script> Header Comment Footer 8 Clarity Educational Community
Header Tag Namespaces Header contains GEL namespace A GEL script is built from qualified elements bound to java code called tags Tags are organized into tag libraries that dictate what can be used in the script CA PPM ships with many out of the box tag libraries. 9 Clarity Educational Community
Header Tag Namespaces, cont. <gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql" xmlns:xog="http://www.niku.com/xog" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <!-- Code goes here.. --> </gel:script> 10 Clarity Educational Community
Tags Tags are grouped in pairs, similar to HTML <core:????></core:????> <core:????/> Each tag should have a closing tag Tag name comes from the library definition xmlns:core="jelly:core" Core, SQL, GEL are most common libraries Syntax <namespace:tag attribute="attribute value"/> 11 Clarity Educational Community
Logging The gel:log tag allows for logging of messages viewable from the CA PPM application You can declare the log to be INFO (Yellow flag) or WARN (Red X) Syntax <gel:log level="INFO">Log Message</gel:log> 12 Clarity Educational Community
Comments Commenting your code will save a number of headaches later on. Comment syntax is the same as HTML. Syntax <!-- comment here--> 13 Clarity Educational Community
Creating Your First Script Follow Along Create a process Go to Studio / Processes / New 14 Clarity Educational Community
Creating Your First Script, cont. Add Primary Object Select Project object, Save and Return 15 Clarity Educational Community
Creating Your First Script, cont. Left click Start Step 16 Clarity Educational Community
Creating Your First Script, cont. Create a new action 17 Clarity Educational Community
Creating Your First Script, cont. Select Custom Script Click Next 18 Clarity Educational Community
Creating Your First Script, cont. Enter action name and id Enter the script into the custom script window <gel:script xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"> <!-- Code goes here.. --> <gel:log level="INFO">My first GEL worked!</gel:log> </gel:script> Validate, then Save and Return 19 Clarity Educational Community
Creating Your First Script, cont. Under Post-Conditions, click Select Step Select Finish step, click add 20 Clarity Educational Community
Creating Your First Script, cont. Click Save and Return Select the Validation tab 21 Clarity Educational Community
Creating Your First Script, cont. Select Validate All and Activate 22 Clarity Educational Community
Creating Your First Script, cont. 1. Go to project list and select a project 2. Click the Processes tab 3. Select the Available sub-page 4. Select the test script, then click Start 23 Clarity Educational Community
Creating Your First Script, cont. 5. Screen flips to initiated processes 6. Click the messages flag 7. Your log entry appears 8. The BPM message shows for each step; this is normal 24 Clarity Educational Community
Questions 25 Clarity Educational Community
Variables Variables are widely used in GEL scripts Declared at the time of use Most tags can set variables Setting a variable <core:set var="var_name">variable value</core:set> Reading a variable ${var_name} Variables are valid for current script only 26 Clarity Educational Community
Persisting Variables Persisting a variable allows makes it accessible throughout the entire process Persisted variables are only valid for the initiated instance. <gel:persist var="var_name">variable_value</gel:persist> 27 Clarity Educational Community
Built-in Variables Each process has three built in variables ${gel_objectInstanceId} Object instance ID of the instance the process is executing on Great for project or idea processes ${gel_processId} ID of the Process itself ${gel_processInstanceId} Process instance ID 28 Clarity Educational Community
Process Parameters <gel:parameter> Allows passing of values into a GEL script from a CA PPM process Inside the GEL script, you can refer to the parameter as you would any other variable by using ${variablename} The optional attribute secure="true" causes CA PPM to hide the actual value with asterisks (*) in the user interface <gel:parameter var="XOGUsername" default="admin"/> <gel:parameter var="XOGPassword" default="password" secure="true"/> 29 Clarity Educational Community
Case Sensitivity Information contained within GEL tags is case sensitive For example, if you declare a variable as follows: <core:set var="v_ProjectID">PRJ-123456</core:set> Then reference the variable as follows: <gel:log>${v_projectid}</gel:log> PRJ-123456 will not output This will not cause an actual error to occur, so you can t catch this error. 30 Clarity Educational Community
Add the Database Data Source 1. Add the SQL tag library to the header xmlns:sql="jelly:sql" 2. Add the niku data source at the beginning of the script <gel:setDataSource dbId="niku"/> 31 Clarity Educational Community
Multiple Data Sources Adding the var tag to the data source allows for multiple data sources <gel:setDataSource dbId="niku" var="clarity"/> <gel:setDataSource dbId="ps_ora" var="peoplesoft"/> 32 Clarity Educational Community
Query the Database Query structure <sql:query dataSource="${clarity}" escapeText="0" var="qry_task"> <![CDATA[ SELECT PRNAME from PRTASK ]]> </sql:query> The VAR is the array this query will be stored as dataSource is the variable of the data source to be used If only one data source, this tag is not needed Always wrap sql statements in the CDATA tag so you can use < and > characters 33 Clarity Educational Community
Query Parameters Passing parameters <sql:query dataSource="${clarity}" escapeText="0" var="qry_task"> <![CDATA[ SELECT PRNAME from PRTASK WHERE PRPROJECTID = ? ]]> <sql:param value="${gel_objectInstanceId}"/> </sql:query> Parameters are in the order of the ? from the query This creates a bind parameter and is more efficient for the database Helps with escaped characters and data types. 34 Clarity Educational Community
Query Results Two ways to get the data out By Index (Order of columns) <core:forEach trim="true" items="${qry_task.rowsByIndex}" var="row"> <gel:log level="INFO">Task Name: ${row[0]}</gel:out> </core:forEach> By column name (Preferred Method) <core:forEach trim="true" items="${qry_task.rows}" var="row"> <gel:log level="INFO">Task Name: ${row.prname}</gel:log> </core:forEach> 35 Clarity Educational Community
Update Query <sql:update dataSource="${clarity}" escapeText="0" var="updateCnt"> <![CDATA[ UPDATE odf_ca_project ocp SET ocp.rego_appr_date = sysdate WHERE ocp.id = ? ]]> <sql:param value="${gel_objectInstanceId}"/> </sql:update> The variable ${updateCnt} contains the number of rows the update statement affects Note: Using CDATA tags in update statements is preferred 36 Clarity Educational Community
Update Query, cont. Processes often require updating the values of specific attributes You can to do this is with a SQL Update statement Keep the following in mind when performing direct database updates Avoid Insert statements these are best suited for XOG Direct database updates will not trigger a process to start; XOG updates will typically trigger processes to start Avoid updating OOTB tables when possible; using XOG will ensure all CA PPM business rules are followed It is generally safe to update custom attributes with a SQL update (these are typically found in tables beginning with odf_ca) Use extra caution within On-demand environments 37 Clarity Educational Community
Delete Query <sql:update dataSource="${clarity}" escapeText="0" var="updateCnt"> <![CDATA[ DELETE FROM Z_CUSTOM_TABLE WHERE id = ? ]]> <sql:param value="${gel_objectInstanceId}"/> </sql:update> The update tag is also used for delete statements 38 Clarity Educational Community
Looping forEach Use forEach to loop through database result sets <core:forEach trim="true" items="${queryResult.rows}" var="row"> .. Do something .. .. Do something .. </core:forEach> 39 Clarity Educational Community
Looping While Loop A While Loop can also be create. <core:while test="${v_counter < 0}"> .. Do Something.. .. Don t forget to update the counter </core:while> 40 Clarity Educational Community
Conditional If/Then Core:if is a simple if/then statement <core:if test="${debug == 1}"> <gel:log level="INFO">XOG URL: ${xogUrl}</gel:log> </core:if> Conditional Expressions == Equals != Not Equals > Greater Than <: Less Than ≥ Greater Than or Equal To ≤ Less Than or Equal To Operational Expressions || && Or And 41 Clarity Educational Community
Conditional Core/Choose/Otherwise core:choose allows you to choose the right path Otherwise is the default action when conditions are not met <core:choose> <core:when test="${row[6] == 'test'}"> </core:when> <core:otherwise> </core:otherwise> </core:choose> 42 Clarity Educational Community
Sending Email GEL can send HTML formatted email <gel:email from="Clarity@noreply.com" fromName="Clarity" subject="Subject Line ${invCode}" to="${toEmail}"> <![CDATA[ Hello,<br> <strong>this is bolded</strong> ]]> </gel:email> Gel:Email uses the mail server set up in the CSA To address can be multiple separated by ; If the address is listed twice it will fail Uses the xmlns:email="jelly:email"namespace 43 Clarity Educational Community
Sending Email, cont. Email can be split up with database queries in the middle <gel:email from="Clarity@noreply.com" fromName="Clarity" subject="Subject Line ${invCode}" to="${toEmail}"> <![CDATA[ Hello,<br><strong>this is bolded</strong> ]]> <sql:query dataSource="${clarity}" escapeText="0" var="qry_res"> <![CDATA[ SELECT PRT.PRPROJECTID = ? ]]> <sql:param value="${gel_objectInstanceId}"/> </sql:query> <core:forEach trim="true" items="${qry_res.rows}" var="row"> ${row.full_name} <br/> </core:forEach> </gel:email> 44 Clarity Educational Community
Exercise Create an on-demand GEL script on the Project object The GEL script should send a single email that contains a list of resources on the project Create the GEL script, and execute it manually on a project Don t forget to add the data source and namespaces Use your own email address 45 Clarity Educational Community
Exercise, cont. Namespaces to use xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql" SQL Script to use <![CDATA[ SELECT FULL_NAME FROM PRTEAM PRT INNER JOIN SRM_RESOURCES SRMR ON PRT.PRRESOURCEID = SRMR.ID WHERE PRT.PRPROJECTID = ? ]]> <sql:param value="${gel_objectInstanceId}"/> 46 Clarity Educational Community
Solution Shell Create the GEL shell structure with namespaces <gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql"> </gel:script> 47 Clarity Educational Community
Solution Data Source Add the Data Source <gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql"> <gel:setDataSource dbId="niku"/> </gel:script> 48 Clarity Educational Community
Solution SQL Query Add the SQL query after the data source <sql:query var="qry_team"> <![CDATA[ SELECT FULL_NAME FROM PRTEAM PRT INNER JOIN SRM_RESOURCES SRMR ON PRT.PRRESOURCEID = SRMR.ID WHERE PRT.PRPROJECTID = ? ORDER BY FULL_NAME ]]> <sql:param value="${gel_objectInstanceId}"/> </sql:query> 49 Clarity Educational Community
Solution Email Body After the data source <gel:email from="clarity_noreply@clarity.com" fromName="Clarity" subject="Project Team Members" to="Your.Email@here.com"> <![CDATA[ Greetings, the following resources on assigned to the project.<br/> ]]> </gel:email> 50 Clarity Educational Community
