AnsweredAssumed Answered

GEL script not sending emails

Question asked by matej256 on Dec 8, 2015
Latest reply on Dec 10, 2015 by Dave_3.0

Hi All,

 

I've a question if somebody experienced in GEL has ever met with issue with not sending emails in GEL script. Mentioned script runs smoothly without any errors and included SQL gives me 4 results which are expected as

sent 4 emails to PMs in Clarity. In spite of that no emails are sent. Sending emails in CLarity is working fine for us (tried another processes) so it's not Email server issue. I've tried to also Query withou Sum, Min, Max function, Withou CDATA also, nothing helped.

 

Could you please advice what it could be? Thanks a lot

 

Matej

<gel:script xmlns:gel = "jelly:com.niku.union.gel.GELTagLibrary"
  xmlns:core = "jelly:core"
  xmlns:sql = "jelly:sql"
  xmlns:soap = "jelly:com.niku.union.gel.SOAPTagLibrary" 
  xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"  
    xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" >

  <gel:parameter default="niku" var="dbID"/>
  <gel:parameter default="http://localhost:8080" var="XOGURL"/>
  <gel:parameter default="***" var="***"/>
  <gel:parameter default="***" secure="true" var="***"/>

  <gel:setDataSource dbId = "niku" var = "DBfetch"/>

   <!-- Retrieve Affected projects with PjMs for who emails will be sent -->

  <core:catch var = "message1">
  <sql:query dataSource = "${DBfetch}" escapeText = "false" var = "sqlResult">
  <![CDATA[
          WITH act_table AS 
  (
  select w.project_code, ii.name, pwv.totalcost, w.transdate start_date, w.transdate end_date, w.lastupdatedate,
  (select email from srm_resources where user_id = ii.manager_id) manager_email,
  (select full_name from srm_resources where user_id = ii.manager_id) manager_name, ii.id project_id
  from ppa_wip w
  join ppa_wip_values pwv on pwv.transno = w.transno 
  inner join inv_investments ii on w.project_code = ii.code 
  Where  w.lastupdatedate like To_char(SYSDATE, 'DD-MON-RR') 
  and pwv.currency_type = 'HOME' 
  and w.status != 2 
  and w.status != 4 
  )
  select act.project_code, act.name, sum(act.totalcost) actuals, to_char(min(act.start_date),'YYYY-MM') start_month, 
  to_char(max(act.start_date),'YYYY-MM') end_month, act.manager_email, act.manager_name, act.project_id
  from act_table act 
  GROUP BY act.project_code, act.name, act.manager_email, act.manager_name, act.project_id
  ]]>
  </sql:query>
  </core:catch>

  <core:catch var = "message">
  <core:forEach items = "${sqlResult.rowsByIndex}" trim="false" var="row">
<gel:email from="clarity@admin.com" fromName="Clarity"
            subject="New actuals received on your project ${row[1]} in Clarity" to="${row[5]}">Dear ${row[6]},&lt;BR>

  Please find following Clarity project data about your project: &lt;BR>

  &lt;BR>
  - Project Code: ${row[0]}&lt;BR>
  - Project Name: ${row[1]}&lt;BR>
  - Updated Actual Costs: ${row[2]}&lt;BR>
  - Start Month Period: ${row[3]}&lt;BR> 
  - End Month Period: ${row[4]}&lt;BR>

  &lt;BR>
  &lt;BR>
  Best regards,&lt;BR>
  Your Clarity Team

  </gel:email>
  </core:forEach>
  </core:catch>

</gel:script>

 

SQL_gel.JPG

 

Process_clarity.jpg

Outcomes