Additional GEL use case info below in case it helps.
PROBLEM : If a resource does not submit their
timesheet on time, there is no way--other than their resource manager notifying
them--to remind them to submit their time sheet.
SOLUTION : To resolve this you can create a
process that would retrieve a list of resources--depending on the criteria you
place--and then email them directly from within Clarity. In our case we will
email both the resource and the resource manager when a timesheet was not
submitted.
REQUIREMENTS :
Clarity
is set up to send emails Understanding
of GEL Knowledge
of SQL Knowledge
of "Job scheduling" in Clarity.
ACTION ITEMS:
Write the
SQL code that returns the desired dataset. In our case we are looking for: Any
resource that is NOT inactive (active or locked), Any
resource that is open for Time Entry, Any
resource that has not created/submitted a timesheet or their timesheet
was rejected, The
status of the timesheet, and The
timesheet in question was last weeks.
Our query returned the following
data:
a. Resource's
Name
b. Resource's
Email address
c. Resource's
Manager's Name
d. Resource's
Manager's Email address
e. Week
of non-submitted timesheet
f.
Status of timesheet.
Write
the GEL code that will run the above SQL code, process the output, and
generate the emails. Create
and Schedule a process.
[left]
Writing the SQL
Before you start, you need to identify the data this query
needs to return and the best place to look for that is the criteria given to
you by your client.
In our case, we needed 6 pieces of information in order to generate
these email notifications. These again where:
Resource's
Name Resource's
Email address Resource's
Manager's Name Resource's
Manager's Email address Week
of non-submitted timesheet Status
of timesheet.
After you have identified the data you require, write the
query and verify its validity in the tool you have access to/prefer (Aqua Data
Studio?) After you have completed this step you can proceed to the next step.
SQL CODE:
Here is a sample SQL query:
SELECT TIME.FULL_NAME
"FULL NAME",
TIME.RES_EMAIL "EMAIL",
TIME.MANAGER "MGR NAME",
TIME.MGR_EMAIL "MGR EMAIL",
TIME.WEEK "WEEK",
TIME.STATUS "STATUS"
FROM (
SELECT SRM.FULL_NAME FULL_NAME, SRM.EMAIL
RES_EMAIL, MGR.FULL_NAME MANAGER, MGR.EMAIL MGR_EMAIL,
TO_CHAR(TP.PRSTART,
'YYYY/MM/DD') || ' - ' || TO_CHAR((TP.PRFINISH - 1), 'YYYY/MM/DD') WEEK,
STAT.NAME STATUS
FROM
PRTIMESHEET TS
INNER
JOIN SRM_RESOURCES SRM ON TS.PRRESOURCEID = SRM.ID
INNER
JOIN PRTIMEPERIOD TP ON TS.PRTIMEPERIODID = TP.PRID AND TP.PRISOPEN = 1
INNER
JOIN CMN_LOOKUPS_V STAT ON TS.PRSTATUS = STAT.LOOKUP_CODE
INNER
JOIN SRM_RESOURCES MGR ON SRM.MANAGER_ID = MGR.USER_ID
INNER
JOIN CMN_SEC_USERS CMN ON SRM.UNIQUE_NAME = CMN.USER_NAME
WHERE
LANGUAGE_CODE = 'en'
AND LOOKUP_TYPE =
'prTimeSheetStatus'
AND PRSTATUS IN (0, 2)
AND CMN.USER_STATUS_ID
IN (200, 202)
AND PRFINISH =
NEXT_DAY(TRUNC(SYSDATE-7), 'SATURDAY')
UNION All
SELECT SRM.FULL_NAME FULL_NAME, SRM.EMAIL
RES_EMAIL, MGR.FULL_NAME MANAGER, MGR.EMAIL MGR_EMAIL,
TO_CHAR(TP.PRSTART,
'YYYY/MM/DD') || ' - ' || TO_CHAR((TP.PRFINISH - 1), 'YYYY/MM/DD') WEEK,
'Not Submitted' STATUS
FROM
SRM_RESOURCES SRM
INNER
JOIN PRJ_RESOURCES ON SRM.ID = PRJ_RESOURCES.PRID AND
PRJ_RESOURCES.PRISOPEN = 1 AND PRISROLE = 0
INNER
JOIN SRM_RESOURCES MGR ON SRM.MANAGER_ID = MGR.USER_ID
INNER
JOIN PRTIMEPERIOD TP ON SRM.IS_ACTIVE = TP.PRISOPEN
INNER
JOIN CMN_SEC_USERS CMN ON SRM.UNIQUE_NAME = CMN.USER_NAME
WHERE
CMN.USER_STATUS_ID IN (200, 202)
AND PRFINISH =
NEXT_DAY(TRUNC(SYSDATE-7), 'SATURDAY')
AND NOT EXISTS
(SELECT 'x' FROM
PRTIMESHEET TS
WHERE
TS.PRRESOURCEID = SRM.ID
AND
TS.PRTIMEPERIODID = TP.PRID)
) TIME
ORDER BY FULL_NAME, WEEK
EXAMPLE SQL DATA SET:
FULL
NAME
EMAIL
MGR
NAME
MGR
EMAIL
WEEK
STATUS
Last,
First_1
User1@xyz.com
Mrg,
Name_1
Mgr1@xyz.com
2006/07/28
- 2006/08/04
Not
Submitted
Last,
First_2
User2@xyz.com
Mrg,
Name_2
Mgr2@xyz.com
2006/07/28
- 2006/08/04
Not
Submitted
Last,
First_3
User3@xyz.com
Mrg,
Name_3
Mgr3@xyz.com
2006/07/28
- 2006/08/04
Rejected
…
…
…
…
…
…
[left]
Writing the GEL
When it comes to GEL, you should consider looking into the
"Clarity Integration Guide" as it provides a lot of info on GEL. Some
of the examples may not run if copied out of the book, but it is a start.
The main component of gel scripting is its starting tag.
There you must define the libraries to use for your script. In our case we will
require the "common" library, the SQL library and the
"Clarity-defined library". The code looks like this:
xmlns:core="jelly:core"
xmlns:sql="jelly:sql"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary">
. . .
Your code goes here!
. . .
When you need to call a function from one of these libraries
you MUST start the tag with the name you gave that library above. For example,
to use sql functions the tags must start with If in the above starting tag you have defined the sql
library as xmlns:my_sql="jelly:sql" then
the tag for the sql query will look like
When it comes to the body of the code it will require two
sections (blocks). One to run the SQL and one to parse the results and send the
emails.
The GEL code to run SQL is straight forward. You create a
connection, you run the SQL. For example:
select * from some_table where something is equal to something
else
The variable "results"
will contain the data returned by this query. The only tricky part is when you
return multiple entries (columns) per row of data. In that case, you will have
to parse the data in order to get the data set to look like the output of the
query (see above in Writing the SQL section)
For that you will require "for"
loops, or in GEL lingo "forEach" .
Here is an example:
do something
This is where the second part of the code comes in. It will process
the data and generate the emails. Email in GEL is also straight forward. Here
is an example:
This is where you enter the text
of the message you want the resource to receive. Please note that this is text.
No formatting is possible
other than what you SEE!
Regards.
The above script will produce an email with the following
message body:
This is where you enter the text
of the message you want the resource to receive. Please note that this is text.
No formatting is possible
other than what you SEE!
Regards.
Finally, to put comments in your code use
GEL SCRIPT:
Putting the whole thing together, here is the GEL script
that worked for us:
Creating and scheduling a process
STEPS :
Create
a process. Give it a Name and an ID but DO NOT ATTACH THAT PROCESS TO AN
OBJECT! Click "Save"
Click
"Start Step"
Click
on "Set Action"
Choose
"Custom Script"
Enter
the script/code, and click on "Save and Continue"
Click
on "Select Step"
Select
"Finish"
Click
on "Save and Continue"
Click
on "Save and Continue", "Continue", or "Save and
Exit" to complete the process. Then go back in the process and
activate the process.
The
final step is to schedule the process. This can be done via "Reports
and Jobs" (on the app side), choose "Jobs", "Available
Jobs" and pick "Execute a process"
GOOD LUCK!