Hidden Automation The Power of GEL Scripting to

Hidden Automation The Power of GEL Scripting to Enhance Functionality www. regoconsulting. com Phone: 1 -888 -813 -0444

Topics ● ● ● ● Background of GEL Basic Script Structure Commonly Used Tags Hands on exercises with creating GEL Scripts Best Practices Real-life Examples Q&A 2 www. regoconsulting. com Phone: 1 -888 -813 -0444

Background of GEL ● GEL (Generic Execution Language) is a tool you can use to turn XML into executable code. It is based on Jelly, a jakarta. apache. org Commons project. It has been extended and embedded into CA Clarity PPM to enable custom logic to solve business problems. ● Additional information can be found in the CA Documentation (CAClarity. PPM_XOG_Developer. Guide_ENU. pdf) and at the Apache Jelly website at http: //jakarta. apache. org/commons/jelly/index. html. 3 www. regoconsulting. com Phone: 1 -888 -813 -0444

GEL Setup ● The GEL run-time is packaged with XOG in the XOG client. Once the client is installed, you can use the GEL command in the bin directory of the XOG client to validate and execute GEL scripts. ● GEL can also be used within Clarity processes. 4 www. regoconsulting. com Phone: 1 -888 -813 -0444

Script Structure Header Comment Footer <gel: script xmlns: core="jelly: core" xmlns: gel="jelly: com. niku. union. gel. GELTag. Library" xmlns: sql="jelly: sql" xmlns: xsd="http: //www. w 3. org/2001/XMLSchema"> <!-- CODE GOES HERE --> </gel: script> 5 www. regoconsulting. com Phone: 1 -888 -813 -0444

GEL Tags A GEL script is an executable XML file that is built from qualified elements bound to Java code called tags. Using namespace declarations, tags are organized into tag libraries which are made available in a script. Hello World Example: <gel: script xmlns: core="jelly: core" xmlns: gel="jelly: com. niku. union. gel. GELTag. Library"> <core: for. Each index. Var='i' begin='1' end='3'> <gel: out>Hello World ${i}!</gel: out> </core: for. Each> </gel: script> ● Note: An entire script always resides within the GEL script tag. 6 www. regoconsulting. com Phone: 1 -888 -813 -0444

Common Namespaces xmlns: core="jelly: core" xmlns: gel="jelly: com. niku. union. gel. GELTag. Library" xmlns: sql="jelly: sql" xmlns: xog=http: //www. niku. com/xog" xmlns: soap="jelly: com. niku. union. gel. SOAPTag. Library" xmlns: soapenv="http: //schemas. xmlsoap. org/soap/envelope/" xmlns: file="jelly: com. niku. union. gel. File. Tag. Library" xmlns: util="jelly: util" xmlns: email="jelly: email" xmlns: ftp="jelly: com. niku. union. gel. FTPTag. Library" 7 www. regoconsulting. com Phone: 1 -888 -813 -0444

Variables are used extensively throughout GEL scripts. Many tags can set variables. An example of a tag that can set variables is core: set. You can use the common syntax ${variable_name} to reference variables. In the Hello World example, 'i' is a variable which is set by the for. Each tag and is incremented with each loop. Hello World Example: <gel: script xmlns: core="jelly: core" xmlns: gel="jelly: com. niku. union. gel. GELTag. Library"> <core: for. Each index. Var='i' begin='1' end='3'> <gel: out>Hello World ${i}!</gel: out> </core: for. Each> </gel: script> 8 www. regoconsulting. com Phone: 1 -888 -813 -0444

Commonly Used Tags ● ● ● ● Conditionals / Loops Variables / Parameters Database Operations Logging Exception SOAP / XOG File Operations Email 9 www. regoconsulting. com Phone: 1 -888 -813 -0444

Conditionals / Loops ● <core: if> <core: if test="${has. Docs}"> … </core: if> ● <core: choose> <core: when test="${row[6]. equals(" )}"> … </core: when> <core: otherwise> … </core: otherwise> </core: choose> 10 www. regoconsulting. com Phone: 1 -888 -813 -0444

Conditionals / Loops cont. ● <core: for. Each> <core: for. Each trim="true" items="${query. Result. rows}" var="row"> … </core: for. Each> ● <gel: for. Each> <gel: for. Each select="$projects. XML/Niku. Data. Bus/Project" var="current. Prj"> … </gel: for. Each> ● Note that there are two separate for. Each loop types. The core version performs basic FOR looping. If you need to retrieve values from an XML document to use in the loop condition (that is, need to use a SELECT clause), then you need the GEL implementation. 11 www. regoconsulting. com Phone: 1 -888 -813 -0444

Variables ● <gel: parameter> This tag allows values to be passed into a GEL script from a CA Clarity PPM process. Inside the GEL script, you can refer to the parameter as you would any other variable (that is, using the ${variablename} syntax). The optional attribute secure="true" causes CA Clarity PPM to hide the actual value in the user interface with asterisks (*). <gel: parameter var="XOGUsername" default="admin"/> <gel: parameter var="XOGPassword" default="password" secure="true"/> 12 www. regoconsulting. com Phone: 1 -888 -813 -0444

Variables cont. ● <core: set> This tag is used to set basic variables; that is, ones that do not need to be extracted from an XML document. Refer to the variable using the ${variablename} syntax. <core: set value="1" var="yes"/> <gel: out>${yes}</gel: out> You can do some basic math on the variable: <gel: out>${yes+2}</gel: out> ● <gel: set> Use this tag when it is necessary to extract the value of the variable from an XML document. This tag differs from the <core: set> tag in that it takes a select attribute which in turn requires an XPath statement. If you are unfamiliar with XPath, think of it as a hierarchy mapping of the XML document. In the example below, the select statement points the way to the Statistics node of a XOG output file. <gel: set as. String="true" select="$result//XOGOutput/Statistics" var="xog. Stats"/> 13 www. regoconsulting. com Phone: 1 -888 -813 -0444

Variables cont. ● <gel: persist> This tag allows you to set variables with a scope that extends beyond the current script. ● <gel: parse> The <gel: parse> tag is used to create an XML document in memory. This is how you will build XOG requests. The tag can be used to generate an entire XML document, or specific nodes that can later be attached into an existing XML document. <gel: parse var="load. Content"> <Niku. Data. Bus xmlns: xsi=http: //www. w 3. org/2001/XMLSchema-instance xsi: no. Namespace. Schema. Location=". . /xsd/nikuxog_resource. xsd"> <Header version="12. 0. 0. 5028" action="write" object. Type="resource“ external. Source=“PS"/> <Resources> <Resource resource. Id="abc" is. Active="true"> <Personal. Information last. Name="doe" first. Name="john" email. Address="jdoe@ca. com"/> </Resources> </Niku. Data. Bus> </gel: parse> 14 www. regoconsulting. com Phone: 1 -888 -813 -0444

Built-in Parameters Custom Action GEL scripts associated with processes have the following parameters available to them: ● Object instance ID If no object is associated with the process, the ID is -1. Otherwise the ${gel_object. Instance. Id} parameter contains the object instance ID. ● Process ID ${gel_process. Id} is the process identifier; all instances of this process share this identifier. ● Process instance ID ${gel_process. Instance. Id} is the process instance identifier; all instances have a unique value. ● All built-in parameters have a "gel_" prefix and are of data type numeric. 15 www. regoconsulting. com Phone: 1 -888 -813 -0444

Case Sensitive Issues Information contained within GEL tags is case sensitive. For example, if you declare a variable as follows: <core: set var="v_Project. ID">PRJ-123456</core: set> Then reference the variable as follows: <gel: out>${v_projectid}</gel: out> This will not output PRJ-123456. However, this will not cause an actual error to occur, so you can’t “catch” this error. 16 www. regoconsulting. com Phone: 1 -888 -813 -0444

Database Operations ● ● ● Datasources SQL Queries SQL Updates SQL Bind Variables / Parameters Transactions 17 www. regoconsulting. com Phone: 1 -888 -813 -0444

Datasources There a couple of methods you can use within GEL to connect to your Clarity database. The recommended method is to use: <gel: set. Data. Source/> The tag takes the database id attribute as follows: <gel: set. Data. Source db. Id="Niku" [var="clarity. DS"]/> The above tag takes the connection properties (including password) from Clarity’s properties (set in the CSA). Niku, in the above example, is the ID of the default internal Clarity database. If you create additional external database definitions, you can reference them in the same manner. For example, if you added a second database connection definition called “ORA-FIN” you could reference it as follows: <gel: set. Data. Source db. Id="ORA-FIN"/> 18 www. regoconsulting. com Phone: 1 -888 -813 -0444

Datasources cont. Note that the var attribute is optional. If you do not specify a variable, the tag uses the default, and all subsequent SQL calls will use the same default. For example, you can do: <gel: set. Data. Source db. Id="Niku"/> <sql: query var="result"> SELECT 1 from dual </sql: query> OR <gel: set. Data. Source db. Id="Niku" var="clarity. DS"/> <sql: query data. Source="${clarity. DS}" var="result"> SELECT 1 from dual </sql: query> 19 www. regoconsulting. com Phone: 1 -888 -813 -0444

Datasources cont. <sql: set. Data. Source> Reasons to use the <sql: set. Data. Source> tag ● You are unable to add the database connection to the CSA ● You need to connect to a DB that isn’t Oracle or SQL Server ● You want to run the script from the command line and don’t have a copy of the properties. xml file on your computer <sql: set. Data. Source url="jdbc: oracle: thin: @localhost: 1521: NIKU” driver="oracle. jdbc. driver. Oracle. Driver” user="${Clarity. User}" password="${Clarity. Password}" var="clarity. DS"/> Note: The above example allows you to have more than one datasource defined, and to specify which datasource to use through the “clarity. DS” variable. 20 www. regoconsulting. com Phone: 1 -888 -813 -0444

SQL Query The following is an example of a simple SQL query called from within a GEL script <sql: query data. Source="${clarity. DS}" escape. Text="0" var="result"> <![CDATA[ SELECT r. full_name res. Name, r. email res. Email FROM srm_resources r ]]> </sql: query> This query returns data in an array (for all intents and purposes) with the name “result. ” Note: Due to the nature of XML, using certain characters (‘<‘, ‘>’) within your query would cause an error without enclosing the query in the CDATA tag. Because of this, it is a best practice to include this tag in all queries. 21 www. regoconsulting. com Phone: 1 -888 -813 -0444

SQL Query cont. You can then access the data in the “result” array using the <core: for. Each> tag. There a couple of different ways to do this: <!-- By Index --> <core: for. Each trim="true" items="${result. rows. By. Index}" var="row"> <gel: out>Resource Name: ${row[0]}</gel: out> </core: for. Each> <!-- By Column Name (Preferred Method) --> <core: for. Each trim="true" items="${result. rows}" var="row"> <gel: out>Resource Name: ${row. res. Name}</gel: out> </core: for. Each> Note: When possible, avoid setting a bunch of variables with the results of the query. 22 www. regoconsulting. com Phone: 1 -888 -813 -0444

SQL Update Often times processes require updating the values of specific attributes. One of the ways to do this is with a SQL Update statement. Keep in mind the following when performing direct database updates ● Generally speaking, it is best to avoid Insert statements – these are best suited for XOG ● Direct database updates will not trigger a process to start – XOG updates typically will trigger processes to start ● Avoid updating OOTB tables when possible – using XOG will ensure all Clarity 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 ● Extra caution should also be used within On-demand environments 23 www. regoconsulting. com Phone: 1 -888 -813 -0444

SQL Update cont. Example <sql: update data. Source="${clarity. DS}" escape. Text="0" var="update. Cnt"> <![CDATA[ UPDATE odf_ca_project ocp SET ocp. rego_appr_date = sysdate WHERE ocp. id = ${gel_object. Instance. Id} ]]> </sql: update> The variable ${update. Cnt} will contain the number of rows that the update statement affects Note: Using CDATA tags in update statements is also preferred. 24 www. regoconsulting. com Phone: 1 -888 -813 -0444

SQL Bind Variables / Parameters Many times SQL statements will include variables that are passed in to the statement. It is best to set these up as parameters within the SQL Statement. This serves a couple of purposes: ● Ensures data types are correct ● Prevents SQL injection ● Allows reuse of SQL statements for performance gains Example: <sql: update data. Source="${clarity. DS}" escape. Text="0" var="update. Cnt"> <![CDATA[ UPDATE odf_ca_project ocp SET ocp. rego_appr_date = sysdate, ocp. rego_resource = ? WHERE ocp. id = ? ]]> <sql: param value="${row. resource. Id}"/> <sql: param value="${gel_object. Instance. Id}"/> </sql: update> Note: You must have a <sql: param> tag for each ? in the SQL statement, even if they are using the same value. The parameters also must be placed in the order that they appear in the statement. 25 www. regoconsulting. com Phone: 1 -888 -813 -0444

SQL Transaction ● Very Useful if you need your updates to be “All or Nothing” <sql: transaction data. Source="${clarity. DS}"> <core: set value="0" var="error. Cnt"/> <core: catch var="sql. Exception"> <sql: update escape. Text="0" var="update. Cnt"> <!-- SQL UPDATE --> </sql: update> </core: catch> <core: if test="${!empty sql. Exception}"> <core: set value="1" var="error. Cnt"/> </core: if> <!-- COMMIT OR ROLLBACK SQL TRANSACTION --> <core: choose> <core: when test="${error. Cnt == 0}"> <!-- COMMIT TRANSACTIONS --> <sql: update>COMMIT</sql: update> </core: when> <core: otherwise> <!-- ROLLBACK TRANSACTIONS --> <sql: update>ROLLBACK</sql: update> </core: otherwise> </core: choose> </sql: transaction> 26 www. regoconsulting. com Phone: 1 -888 -813 -0444

Logging Messages ● Use this tag to insert status messages into the process engine log table ● Very useful when debugging scripts ● Avoid using too many logging statements, as it can impact performance ● Different levels of logging – INFO, WARN, ERROR <gel: log level="INFO">This is an example log message. </gel: log> 27 www. regoconsulting. com Phone: 1 -888 -813 -0444

Exceptions Use the GEL fault-handling tags to catch exceptions and exit gracefully when a process failure occurs. Use the <core: catch> tag to capture exceptions into a variable. Outside of the catch tags, you can check the variable and write it to the console. Example: <core: catch var="sql. Exception"> <sql: query data. Source="${clarity. DS}" escape. Text="0" var="result"> <![CDATA[ SELECT r. id, FROM srm_resources r ]]> </sql: query> </core: catch> <core: if test="${!empty sql. Exception}"> <gel: log level="ERROR">Exception: ${sql. Exception}</gel: log> </core: if> 28 www. regoconsulting. com Phone: 1 -888 -813 -0444

Hands On Exercises Clarity Environment: http: //54. 193. 48. 58: 8081/niku/nu#action: home. Action. Id Username: regou Password: Clarity 123 Note: When creating processes, please distinguish your process from others by using your name in the process name and id Example: 29 www. regoconsulting. com Phone: 1 -888 -813 -0444

Limitations to Rego U Environment ● URLs are not set up correctly in the properties. xml file. This means that if you need to reference the URL, you’ll have to hard-code it. Example: <gel: parameter var=“URL" default="http: //54. 193. 48. 58: 8081"/> ● Caching Issues 30 www. regoconsulting. com Phone: 1 -888 -813 -0444

Exercise #1 Create a process that uses a GEL script to update the following fields on the project object: Approved By (rego_appr_by): Resource that started the process Approval Date (rego_appr_date): Today’s Date Note: Environment is an Oracle environment Approved By field uses the Resource Browse lookup (srm_resources. id) 31 www. regoconsulting. com Phone: 1 -888 -813 -0444

Exercise #1 Hint SQL to update project fields: UPDATE odf_ca_project ocp SET ocp. rego_appr_date = sysdate, ocp. rego_appr_by = (SELECT r. id FROM bpm_run_processes brp JOIN srm_resources r ON brp. initiated_by = r. user_id WHERE brp. id = ? ) WHERE ocp. id = ? 32 www. regoconsulting. com Phone: 1 -888 -813 -0444

Exercise #1 Review 33 www. regoconsulting. com Phone: 1 -888 -813 -0444

SOAP / XOG By including the SOAP and XOG namespaces in GEL scripts, you give GEL the ability to communicate with the XOG web service. You must package each invocation in a proper SOAP envelope. The following steps must be used within a GEL script to communicate with the XOG web service: ● ● ● Include the proper namespaces Obtain a session ID Create the XML file to send Execute the XOG Parse the Results Logout 34 www. regoconsulting. com Phone: 1 -888 -813 -0444

XOG - Include the proper namespaces <gel: script xmlns: core="jelly: core“ xmlns: gel="jelly: com. niku. union. gel. GELTag. Library“ xmlns: xog="http: //www. niku. com/xog" xmlns: soap="jelly: com. niku. union. gel. SOAPTag. Library" xmlns: soapenv="http: //schemas. xmlsoap. org/soap/envel ope/"> 35 www. regoconsulting. com Phone: 1 -888 -813 -0444

XOG - Obtain a Session ID <!-- Get session. Id by username --> <gel: parameter var="username" default="admin"/> <core: new class. Name="com. niku. union. security. Default. Security. Identifier" var="sec. Id" /> <core: invoke. Static var="user. Session. Ctrl" class. Name="com. niku. union. security. User. Session. Controller. Factory" method="get. Instance" /> <core: set var="sec. Id" value="${user. Session. Ctrl. init(username, sec. Id)}"/> <core: set var="XOGUsername" value="${sec. Id. get. User. Name()}"/> <core: set var="session. ID" value="${sec. Id. get. Session. Id()}"/> <core: choose> <core: when test="${session. ID == null}"> <gel: log level="ERROR"> Unable to obtain a Session ID. </gel: log> </core: when> <core: otherwise> <!-- Execute XOG --> </core: otherwise> </core: choose> 36 www. regoconsulting. com Phone: 1 -888 -813 -0444

XOG - Create the XML File Example: <gel: parse var="user. XML"> <Niku. Data. Bus xmlns: xsi="http: //www. w 3. org/2001/XMLSchema-instance" xsi: no. Namespace. Schema. Location=". . /xsd/nikuxog_user. xsd"> <Header action="write" external. Source="NIKU" object. Type="user" version="13. 2. 0. 472"/> <Users> <User external. Id=" " is. LDAP="false" ui. Theme. Default. Partition. Code=" " user. Language="English" user. Locale="en_US” user. Name="${row. user. Name}" user. Status="${row. user. Status}" user. Timezone="America/Los_Angeles" user. Type="INTERNAL"> <Personal. Information email. Address="${row. email}" first. Name="${row. first. Name}" last. Name="${row. last. Name}"/> <Resource resource. Id="${row. resource. Id}"/> <Groups/> </Users> </Niku. Data. Bus> </gel: parse> 37 www. regoconsulting. com Phone: 1 -888 -813 -0444

XOG - Execute the XOG <!-- Execute XOG --> <soap: invoke endpoint=“internal" var="result"> <soap: message> <soapenv: Envelope xmlns: soapenv="http: //schemas. xmlsoap. org/soap/envelope/" xmlns: xog="http: //www. niku. com/xog"> <soapenv: Header> <xog: Auth> <xog: Session. ID>${session. ID}</xog: Session. ID> </xog: Auth> </soapenv: Header> <soapenv: Body> <gel: include select="$user. XML"/> </soapenv: Body> </soapenv: Envelope> </soap: message> </soap: invoke> 38 www. regoconsulting. com Phone: 1 -888 -813 -0444

XOG - Parse the Results <!-- Parse Results --> <gel: set as. String="true" select="$result//XOGOutput/Status/@state" var="XOGState"/> <core: choose> <!-- Success --> <core: when test="${XOGState == 'SUCCESS'}"> <gel: set as. String="true" select="$result//XOGOutput/Statistics" var="xog. Stats"/> <gel: log level="INFO">User XOG Stats: ${xog. Stats} </gel: log> </core: when> <!-- Failure --> <core: otherwise> <gel: log level="WARN"><gel: expr select="$user. XML/"/></gel: log> <gel: log level="ERROR"><gel: expr select="$result/"/></gel: log> </core: otherwise> </core: choose> 39 www. regoconsulting. com Phone: 1 -888 -813 -0444

XOG - Logout <!-- Logout XOG--> <soap: invoke endpoint=“internal" var="result"> <soap: message> <soapenv: Envelope xmlns: soapenv="http: //schemas. xmlsoap. org/soap/envelope/" xmlns: xog="http: //www. niku. com/xog"> <soapenv: Header> <xog: Auth> <xog: Session. ID>${session. ID}</xog: Session. ID> </xog: Auth> </soapenv: Header> <soapenv: Body> <xog: Logout/> </soapenv: Body> </soapenv: Envelope> </soap: message> </soap: invoke> 40 www. regoconsulting. com Phone: 1 -888 -813 -0444

Exercise #2 Create a process that can be initiated from a custom object for a Project Request that will XOG in a new project • Create project using the Name and ID of the Project Request 41 www. regoconsulting. com Phone: 1 -888 -813 -0444

Exercise #2 Hint Sample Project XOG: <Niku. Data. Bus xmlns: xsi="http: //www. w 3. org/2001/XMLSchema-instance" xsi: no. Namespace. Schema. Location=". . /xsd/nikuxog_project. xsd"> <Header version="6. 0. 11" action="write" object. Type="project" external. Source="NIKU"/> <Projects> <Project name="Test Project" project. ID="PR 1234" /> </Projects> </Niku. Data. Bus> SQL to get Project Request Name and ID: SELECT FROM WHERE pr. code prj. Code, pr. name prj. Name odf_ca_rego_prj_req pr pr. id = ? 42 www. regoconsulting. com Phone: 1 -888 -813 -0444

Exercise #2 Review 43 www. regoconsulting. com Phone: 1 -888 -813 -0444

File Operations GEL can open a file (and if it is an XML file or a comma-delimited file, parse out all the nodes and attributes), read the file, and write to it. It can also perform FTP operations on files. It cannot, however, create a directory to put files in, move files around, or delete files after it is done with them. 44 www. regoconsulting. com Phone: 1 -888 -813 -0444

File Operations – Read File <gel: script xmlns: core="jelly: core" xmlns: gel="jelly: com. niku. union. gel. GELTag. Library" xmlns: xsd="http: //www. w 3. org/2001/XMLSchema" xmlns: files="jelly: com. niku. union. gel. File. Tag. Library"> <gel: parameter var="v. File. Name" default="/fs 0/clarity 1/share/RESOURCES. CSV"/> <files: read. File file. Name="${v. File. Name}" delimiter="|" var="v. Resource. Data" embedded="false"/> <core: for. Each items="${v. Resource. Data. rows}" var="row" begin="1" end="10"> <gel: log level="INFO"> Resource Last Name: ${row[0]} </gel: log> <gel: log level="INFO"> Resource First Name: ${row[1]} </gel: log> </core: for. Each> </gel: script> 45 www. regoconsulting. com Phone: 1 -888 -813 -0444

File Operations – Write File <file: write. File delimiter=", " embedded="false" file. Name=" Resources. csv "> <sql: query data. Source="${clarity. DS}" escape. Text="0" var="result"> <![CDATA[ SELECT u. first_name first. Name, u. last_name last. Name, u. user_name user. Name FROM cmn_sec_users u WHERE u. user_status_id = 200 ]]> </sql: query> <core: for. Each items="${result. rows}" trim="true" var="row"> <file: line> <file: column value="${row. user. Name}"/> <file: column value="${row. last. Name}"/> <file: column value="${row. first. Name}"/> </file: line> </core: for. Each> </file: write. File> 46 www. regoconsulting. com Phone: 1 -888 -813 -0444

Email GEL scripts are often used to send emails. Using a GEL script notification allows for a lot more flexibility than sending an action item. There are two types of email tags, <gel: email> and <email: email>. It is recommended to use the <gel: email> tag where possible. 47 www. regoconsulting. com Phone: 1 -888 -813 -0444

Email cont. <gel: email> ● Email server information is derived from the properties. xml of the installation. ● Supports HTML Example: <gel: script xmlns: gel="jelly: com. niku. union. gel. GELTag. Library"> <gel: email from="clarity-do-not-reply@ca. com" subject="Clarity - Test Email" to="john@gmail. com"> <![CDATA[ This is a test email. <br/> ---------------------------------<br/> This is an automated message, please do not reply. ]]> </gel: email> </gel: script> 48 www. regoconsulting. com Phone: 1 -888 -813 -0444

Email cont. <email: email> ● Mail Server must be specified within the tag ● Does not support HTML ● Supports Attachments Example: <gel: script xmlns: core="jelly: core" xmlns: gel="jelly: com. niku. union. gel. GELTag. Library" xmlns: email="jelly: email"> <core: invoke. Static class. Name="java. lang. System" method="getenv" var="NIKU_HOME"> <core: arg value="NIKU_HOME"/> </core: invoke. Static> <gel: parse file="${NIKU_HOME}/config/properties. xml" var="properties"/> <gel: set as. String="true" select="$properties/mail. Server/@host" var="mail. Server"/> <email: email to="john@gmail. com" from="clarity-do-not-reply@ca. com" subject=“app-ca. log file" server="${mail. Server}" attach="${NIKU_HOME}/logs/app-ca. log"> App-ca. log File </email: email> </gel: script> 49 www. regoconsulting. com Phone: 1 -888 -813 -0444

Exercise #3 Write a script that will email a list of all active projects with managers that are not active users in Clarity to the resources within the following group: 50 www. regoconsulting. com Phone: 1 -888 -813 -0444

Exercise #3 Hint SQL to get Resources in Group: SELECT r. first_name || ' ' || r. last_name res. Name, r. email res. Email FROM cmn_sec_user_groups ug JOIN cmn_sec_groups g ON ug. group_id = g. id AND g. group_code = 'regou_gel_script' JOIN srm_resources r ON r. user_id = ug. user_id SQL to get Projects: SELECT i. id prj. Id, i. code prj. Code, i. name prj. Name, r. full_name prj. Mgr FROM inv_investments i JOIN inv_projects ip ON ip. prid = i. id AND ip. is_template = 0 AND ip. is_program = 0 AND (i. purge_flag = 0 OR i. purge_flag IS NULL) JOIN odf_ca_project ocp ON i. id = ocp. id JOIN cmn_sec_users u ON i. manager_id = u. id AND u. user_status_id != 200 JOIN srm_resources r ON u. id = r. user_id Send Emails from the following email address: technical@regoconsulting. com 51 www. regoconsulting. com Phone: 1 -888 -813 -0444

Exercise #3 Review 52 www. regoconsulting. com Phone: 1 -888 -813 -0444

Best Practices ● If a variable can be changed by an admin, or stores a password, use the <gel: parameter> tag instead of <core: set> ● Comment your code ● Properly format and indent your code ● Place all code within the <gel: script> tags, even comments ● Avoid excessive logging, but keep enough for debugging purposes ● When possible, pull server info from properties file on server Example: <core: invoke. Static class. Name="java. lang. System" method="getenv" var="NIKU_HOME"> <core: arg value="NIKU_HOME"/> </core: invoke. Static> <gel: parse file="${NIKU_HOME}/config/properties. xml" var="properties"/> <gel: set as. String="true" select="$properties/web. Server/web. Server. Instance[@id='app']/@ssl. Entry. Url" var="URL"/> 53 www. regoconsulting. com Phone: 1 -888 -813 -0444

Real Life Examples ● ● Integrations with HR Integrations with Financial systems Timesheet Notifications Updating Resource Rights ● What have you used GEL scripts for? 54 www. regoconsulting. com Phone: 1 -888 -813 -0444

Questions Contact US 888. 813. 0444 Email Contact info@regoconsulting. com Web Site www. regoconsulting. com 55 www. regoconsulting. com Phone: 1 -888 -813 -0444