AnsweredAssumed Answered

Process Ran Successfully but No emails were Triggered

Question asked by deepakallscripts on Mar 25, 2014
Latest reply on Mar 27, 2014 by Gurjeet.S

We have a process running from 2013 till date. Process is scheduled to run on every monday @12 PM. Process triggers email to managers who haven't approved timesheets submitted to them.

On last monday, Process executed completed successfully but none of email's were triggered. Till date all instances ran successfully except of 2. After looking into log, I found that those 2 instance were initiated @12.00 PM but their steps were Triggered at 11.59 AM instead of 12.00 PM (Please see attached snapshots) . For other instances, I can see compelete log of other instances.

Third Image Shows sample log of instance which ran successfully

Please see attached 

 

Gel script which i am executing inside Process: 

 

<gel:script xmlns:core="jelly:core"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:sql="jelly:sql" xmlns:util="jelly:util" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <!-- Parameters for gel script used for email notifications.              -->
    <gel:parameter default="Clarity.Admin@mygroup.com " var="EmailFrom"/>
    <gel:parameter default="Clarity Admin" var="EmailFromName"/>
    <gel:parameter default="Clarity.Admin@mygroup.com" var="ClarityAdmin"/>
    <gel:parameter var="Resource_Manager_flag"/>
    <!-- GEL script for PM.-->
    <!-- Gel tag for database logon. -->
    <gel:setDataSource dbId="niku" var="connect"/>
    <core:set value="0" var="row_count"/>
    <core:set value="${messagea} &#xa;${statmsg}" var="messagea"/>
    <!-- Gel tag for web URL.  No need to hard code the web URL        -->
    <gel:parse file="../config/properties.xml" var="properties"/>
    <util:tokenize delim="/" var="entryUrl">
        <gel:expr select="$properties//webServerInstance[@id='app']/@entryUrl"/>
    </util:tokenize>
    <core:set value="http://${entryUrl[1]}" var="WebServerid"/>
<core:set value="${WebServerid.substring(0,31)}" var="WebServerid"/>  <!-- Using substring functionality because entry has the port 80 -->
 
    <!-- Log step action and begin getting the results for action items to be -->
    <!--
  <gel:log category="step id ${gel_stepInstanceId}" level="info"/>
  <gel:log category="Get action items for reminder" level="info"/>  
  -->
    <gel:log level="INFO">Clarity Server:  ${WebServerid}</gel:log>   
    <!-- Check to see if the server is PROD or a lower environment.  This is necessary in case the lower environment is refreshed in order -->
    <!-- to prevent unnecessary emails being sent from the lower environments.  -->
    <core:switch on="${WebServerid}">
        <core:case value="http://MyserverName.com">
            <sql:query dataSource="${connect}" escapeText="false" var="results"><![CDATA[
          SELECT  
SR.MANAGER_ID MANAGERID,
(select FULL_NAME from niku.SRM_RESOURCES where USER_ID = SR.MANAGER_ID) RESMGR,
(select EMAIL from niku.SRM_RESOURCES where USER_ID = SR.MANAGER_ID) RESMGR_EMAIL
 
FROM (SELECT DISTINCT PR.PRID RESOURCE_ID, TP.PRID PRTIMEPERIODID FROM     niku.PRTIMEPERIOD TP, niku.PRJ_RESOURCES PR   WHERE   PR.PRTRACKMODE <> 0 AND TP.PRISOPEN  <> 0) AS RESTP
LEFT JOIN niku.PRTIMEPERIOD TP ON (RESTP.PRTIMEPERIODID=TP.PRID)
LEFT JOIN niku.PRJ_RESOURCES PR ON (RESTP.RESOURCE_ID = PR.PRID)
LEFT OUTER JOIN niku.PRTIMESHEET TS ON (RESTP.PRTIMEPERIODID=TS.PRTIMEPERIODID AND RESTP.RESOURCE_ID=TS.PRRESOURCEID)
LEFT OUTER JOIN niku.PRTIMEENTRY TE ON TS.PRID=TE.PRTIMESHEETID
LEFT OUTER JOIN niku.PRTIMESHEET ADJ_TS ON TS.PRID=ADJ_TS.PRADJUSTEDID,
SRM_RESOURCES SR
WHERE SR.ID = RESTP.RESOURCE_ID
-- AND SR.IS_ACTIVE = 1 AND (   (TS.PRSTATUS IN (0,2) OR TS.PRID IS NULL) OR TS.PRSTATUS = 1 OR TS.PRSTATUS = 3 OR TS.PRSTATUS > 3)
AND SR.IS_ACTIVE = 1 
AND ((TS.PRSTATUS IN (0,1,2) OR TS.PRID IS NULL) )
and SR.EMAIL like '%@%'
and PR.PRISOPEN = 1
and (SR.DATE_OF_TERMINATION > GETDATE() OR SR.DATE_OF_TERMINATION IS NULL)
and CONVERT(VARCHAR(8),TP.PRSTART,1 )  = CONVERT(VARCHAR(8),getdate() -8,1 )
--and CONVERT(VARCHAR(8),TP.PRFINISH,1 ) = CONVERT(VARCHAR(8),getdate() ,1 )
and CONVERT(VARCHAR(8),TP.PRSTART,1 )  > = SR.DATE_OF_HIRE
and SR.MANAGER_ID is not null
 
GROUP BY 
SR.MANAGER_ID
 
HAVING     1=1
 
    ]]></sql:query>
            <gel:log level="INFO">Query returned</gel:log>
            <!-- Loop through the result set to log the messages to be sent and to    -->
            <!-- send emails to the required users along with managers -->
            <core:forEach items="${results.rowsByIndex}" trim="true" var="row">
                <core:forEach indexVar="i"
                    items="${results.columnNames}" var="field">
                    <core:set value="${row[i]}" var="RM_${field}"/>
                </core:forEach>
                <gel:log level="INFO">Manager ID:  ${RM_MANAGERID}, RM Name:  ${RM_RESMGR}, RM Email:  ${RM_RESMGR_EMAIL} </gel:log>
                <!-- Second query to get each individual Resource    -->
                <sql:query dataSource="${connect}" escapeText="false" var="results2"><![CDATA[
SELECT  
ROW_NUMBER() OVER(ORDER BY tp.prid) DIMKEY,
SR.ID RES_ID,
SR.USER_ID USER_ID,
SR.first_name + ' ' + sr.last_name FULLNAME,
SR.UNIQUE_NAME RES_UNIQUE_NAME,
SR.EMAIL RES_EMAIL,
SR.MANAGER_ID MANAGERID,
(select FULL_NAME from niku.SRM_RESOURCES where USER_ID = SR.MANAGER_ID) RESMGR,
(select EMAIL from niku.SRM_RESOURCES where USER_ID = SR.MANAGER_ID) RESMGR_EMAIL,
PR.PRISOPEN RES_OPEN,
TP.PRID TPID,
TP.PRSTART TS_START,
CONVERT(VARCHAR(8),TP.PRSTART,1) as START_FORMAT,
TP.PRFINISH TS_FINISH,
TP.PRISOPEN TP_OPEN,
(CASE WHEN TS.PRSTATUS=1 THEN 1 WHEN TS.PRSTATUS=2 THEN 2 WHEN TS.PRSTATUS=3 THEN 3 WHEN TS.PRSTATUS=4 THEN 4 WHEN TS.PRSTATUS=5 THEN 4 ELSE 0 END) TS_STATUS,
(CASE WHEN TS.PRSTATUS=1 THEN 'Submitted' WHEN TS.PRSTATUS=2 THEN 'Returned' WHEN TS.PRSTATUS=3 THEN 'Approved' WHEN TS.PRSTATUS=4 THEN 'Posted' WHEN TS.PRSTATUS=5 THEN 'Posted' ELSE 'Unsubmitted' END) TS_STATUS_DISPLAY,
(CASE WHEN SR.RESOURCE_TYPE=0 THEN ISNULL(SUM(TE.PRACTSUM)/3600, 0) WHEN SR.RESOURCE_TYPE=1 THEN ISNULL(SUM(TE.PRACTSUM)/3600, 0) ELSE ISNULL(SUM(TE.PRACTSUM), 0) END) TS_HOURS,
(CASE WHEN TS.PRISADJUSTMENT=0 THEN 0 WHEN TS.PRISADJUSTMENT=1 THEN 1 WHEN TS.PRISADJUSTMENT IS NULL THEN 0   ELSE 0 END) TS_IS_ADJUSTMENT,
(CASE WHEN TS.PRSTATUS=5 THEN 1 ELSE 0 END) TS_IS_ADJUSTED,
SR.PERSON_TYPE RESOURCETYPE,
(CASE WHEN SR.PERSON_TYPE=300 THEN 'Employee' WHEN SR.PERSON_TYPE=301 THEN 'Contractor' END) RESOURCETYPEDISPLAY,
sr.full_name + ' - ' + (CASE WHEN TS.PRSTATUS=1 THEN 'Submitted' WHEN TS.PRSTATUS=2 THEN 'Returned' WHEN TS.PRSTATUS=3 THEN 'Approved' WHEN TS.PRSTATUS=4 THEN 'Posted' WHEN TS.PRSTATUS=5 THEN 'Posted' ELSE 'Unsubmitted' END) + ' - ' + CONVERT(VARCHAR(8),TP.PRSTART,1) + ' - ' + convert(varchar,(CASE WHEN SR.RESOURCE_TYPE=0 THEN ISNULL(SUM(TE.PRACTSUM)/3600, 0) WHEN SR.RESOURCE_TYPE=1 THEN ISNULL(SUM(TE.PRACTSUM)/3600, 0) ELSE ISNULL(SUM(TE.PRACTSUM), 0) END)) + ' hours' EMAIL_DISPLAY
 
FROM (SELECT DISTINCT PR.PRID RESOURCE_ID, TP.PRID PRTIMEPERIODID FROM     niku.PRTIMEPERIOD TP, niku.PRJ_RESOURCES PR   WHERE   PR.PRTRACKMODE <> 0 AND TP.PRISOPEN  <> 0) AS RESTP
LEFT JOIN niku.PRTIMEPERIOD TP ON (RESTP.PRTIMEPERIODID=TP.PRID)
LEFT JOIN niku.PRJ_RESOURCES PR ON (RESTP.RESOURCE_ID = PR.PRID)
LEFT OUTER JOIN niku.PRTIMESHEET TS ON (RESTP.PRTIMEPERIODID=TS.PRTIMEPERIODID AND RESTP.RESOURCE_ID=TS.PRRESOURCEID)
LEFT OUTER JOIN niku.PRTIMEENTRY TE ON TS.PRID=TE.PRTIMESHEETID
LEFT OUTER JOIN niku.PRTIMESHEET ADJ_TS ON TS.PRID=ADJ_TS.PRADJUSTEDID,
SRM_RESOURCES SR
WHERE SR.ID = RESTP.RESOURCE_ID
-- AND SR.IS_ACTIVE = 1 AND (   (TS.PRSTATUS IN (0,2) OR TS.PRID IS NULL) OR TS.PRSTATUS = 1 OR TS.PRSTATUS = 3 OR TS.PRSTATUS > 3)
AND SR.IS_ACTIVE = 1 
AND ((TS.PRSTATUS IN (0,1,2) OR TS.PRID IS NULL) )
and SR.EMAIL like '%@%'
and PR.PRISOPEN = 1
and (SR.DATE_OF_TERMINATION > GETDATE() OR SR.DATE_OF_TERMINATION IS NULL)
and CONVERT(VARCHAR(8),TP.PRSTART,1 )  = CONVERT(VARCHAR(8),getdate() -8,1 )
--and CONVERT(VARCHAR(8),TP.PRFINISH,1 ) = CONVERT(VARCHAR(8),getdate() ,1 )
and CONVERT(VARCHAR(8),TP.PRSTART,1 )  > = SR.DATE_OF_HIRE
and SR.MANAGER_ID = ${RM_MANAGERID}
and SR.MANAGER_ID is not null
 
 
GROUP BY SR.ID,
SR.USER_ID,
PR.PRID,
sr.full_name,
SR.first_name + ' ' + sr.last_name,
SR.UNIQUE_NAME,
SR.EMAIL,
SR.MANAGER_ID,
TP.PRSTART,
TP.PRFINISH,
TP.PRID,
TP.PRISOPEN,
SR.RESOURCE_TYPE,
TS.PRID,
TS.PRSTATUS,
ADJ_TS.PRID,
TS.PRISADJUSTMENT,
TS.PRSTATUS,
PR.PRISOPEN,
SR.PERSON_TYPE
HAVING     1=1
 
]]></sql:query>
                <!-- Loop through the result set to log the messages to be sent and to  -->
                <core:set value="" var="message1"/>
                <core:set value="" var="message2"/>
                <core:set value="" var="messageurl"/>
                <core:set
                    value="${message1}Resource Manager - The following Clarity timesheets need to be submitted and approved by Monday, 11pm.  If you need assistance please email IS PMO Support.&#xa;&#xa;" var="message1"/>
                <core:forEach items="${results2.rowsByIndex}"
                    trim="true" var="row2">
                    <core:forEach indexVar="i"
                        items="${results2.columnNames}" var="field">
                        <core:set value="${row2[i]}" var="SQ_${field}"/>
                    </core:forEach>
                    <core:catch var="exInForEachLoop">
                        <gel:log level="INFO">Resource Manager Email Reminder for Resource Manager:  ${RM_RESMGR}, and resource:  ${SQ_FULLNAME}</gel:log>
                        <core:set
                            value="${message1}${SQ_EMAIL_DISPLAY}&#xa;&#xa;" var="message1"/>
                        <core:set
                            value="Please log into Clarity to approve the Timesheet(s), click on the following link: &#xa;${WebServerid}/niku/app" var="messageurl"/>
                    </core:catch>
                </core:forEach>
                <core:set value="${RM_RESMGR_EMAIL}" var="aiowner_email"/>
                <core:choose>
                    <core:when test="${aiowner_email != null}">
                        
 
          <gel:email from="${EmailFrom}"
              fromName="${EmailFromName}"
              subject="ACTION REQUIRED:  Resource Manager - Outstanding Clarity Timesheets for Start Date:  ${SQ_START_FORMAT}" to="${aiowner_email}">
${message1}
${messageurl}
 
Clarity Admin
Please do not reply to this email.
 
          </gel:email>
 
                    </core:when>
                    <core:otherwise>
                        <gel:log level="INFO">No Email Id found for timesheet owner to send email. </gel:log>
                    </core:otherwise>
                </core:choose>
            </core:forEach>
        </core:case>
        <!-- end for check on server -->
    </core:switch>
</gel:script>

 

Attachments

Outcomes