Clarity

  • 1.  Xogging date changes onto Ideas

    Posted Sep 30, 2016 05:59 PM

    We have a requirement to 'kick forward' any ideas lingering in the first stage of our demand management process.

    Support for Duration Based Ideas

    Slipping & Sliding Ideas.

     

    I have a Process/GEL that *almost* works.

     

    I'm planning on scheduling the process with the Execute a Process job. Early tests found date changes on Idea Start & Finish would have conflicts with time slicing and Allocation jobs. I've attempted to box those out by setting the following jobs as incompatible. 

     

    Unfortunately I'm still experiencing deadlock issues. The process takes minutes to run. I think what's happening is the Execute a Process kicks off the process then terminates in about a second, making all the incompatible jobs listed above irrelevant. Notice how Investment Allocation fires off about a minute into my process run.

     

     

    ... while the kickout ideas process is running...

     

    Is there any way to make execute a process *persistent* with the duration of the process it's running in order for the incompatabilites to do their thing?

     

    I'm simply pulling a list of ideas, calculating new starts & finishes then xogging the new dates on blindly in a forEach loop. The deadlock errors and rollbacks are all database level, so nothing errors in XOG. I can imagine an architecture where I build in some try-catch stuff and check the dates for success & keep running until the result is as expected - but this seems a little too brute force for this problem.

     

    Is there a different way to architect a solution to 'kicking ideas out 6 months'? Has anyone already developed a solution for something like this they can share?

     

     

    <!-- Kick Out Ideas in Intake Due to start within 60 days -->
    <gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
      xmlns:core="jelly:core"
      xmlns:util="jelly:util"
      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:jxml="jelly:xml"
      xmlns:xog="http://www.niku.com/xog"
      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <gel:parameter default="your_xog_username" var="xogUsername"/>
    <gel:parameter default="your_xog_password" secure="true" var="xogPassword"/>

    <!-- Get the list of ideas -->
    <gel:setDataSource dbId="niku"/>
    <sql:query var="data" escapeText="0">
              <![CDATA[SELECT
              I.NAME idea_name
              , I.CODE idea_code
              , (SELECT CONVERT(char(10), DATEADD(mm, DATEDIFF(mm, 0, I.SCHEDULE_START) + 6, 0), 126))+'T00:00:00' idea_start_plus_6
              , (SELECT CONVERT(char(10), DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.SCHEDULE_FINISH) + 7, 0)), 126))+'T00:00:00' idea_finish_plus_6
              FROM INV_INVESTMENTS I
              WHERE I.ODF_OBJECT_CODE = 'Idea'
              AND I.IS_ACTIVE = 1
              AND I.STAGE_CODE = 'osumc_intake'
              AND I.SCHEDULE_START  < (SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 2, 0)))]]>
    </sql:query>

    <!--  Get xog URL from application by fpena -->
    <core:invokeStatic className="com.niku.union.config.ConfigurationManager" method="getInstance" var="config"/>
       <core:set var="v_IsLocal">0</core:set>
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getId()}" var="WebServerid"/>
    <core:if test="${WebServerid == 'app'}">
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getAddress()}" var="AddressIp"/>
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getSslEntryUrl()}" var="EntryURL"/>
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getSslPort()}" var="Port"/>
      <core:if test="${EntryURL == 'http://localhost' }">
       <gel:log level="info" message="NULL ADDRESS IS LOCAL... "/>
       <core:set var="v_IsLocal">1</core:set>
       <core:set var="AddressIp">${EntryURL}</core:set>
      </core:if>
    </core:if>
    <core:if test="${WebServerid == 'nsa'}">
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(1).getAddress()}" var="AddressIp"/>
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(1).getEntryUrl()}" var="EntryURL"/>
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(1).getPort()}" var="Port"/>
      <core:if test="${EntryURL == 'http://localhost' }">
       <gel:log level="info" message="NULL ADDRESS IS LOCAL... "/>
       <core:set var="v_IsLocal">1</core:set>
       <core:set var="AddressIp">${EntryURL}</core:set>
      </core:if>
    </core:if>
    <core:if test="${Port != null }">
      <core:set value="${EntryURL}" var="EntryURL"/>
    </core:if>
    <core:if test="${v_IsLocal == 0 }">
      <core:if test="${AddressIp != '' }">
       <core:set value="http://${AddressIp}" var="EntryURL"/>
      </core:if>
    </core:if>
       <core:set value="${config.getProperties().getDirectories().getInstallDir()}" var="NIKUHOME"/>
       <core:set value="${EntryURL}/niku/xog" var="xogURL"/>

    <!-- Log into xog and get a session ID -->
    <soap:invoke endpoint="${xogURL}" var="auth">
      <soap:message>
       <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
         <soapenv:Header/>
          <soapenv:Body>
           <xog:Login>
            <xog:Username>${xogUsername}</xog:Username>
            <xog:Password>${xogPassword}</xog:Password>
           </xog:Login>
          </soapenv:Body>
       </soapenv:Envelope>
      </soap:message>
    </soap:invoke>

    <!-- Checking whether a sessionID is returned. If not, it means that login was unsuccessful -->
    <gel:set asString="true" select="$auth/SOAP-ENV:Envelope/SOAP-ENV:Body/xog:SessionID/text()" var="sessionID"/>
    <core:choose>
       <core:when test="${sessionID == null}">
        <gel:log>Couldn't log in. Please check the hostname/username/password.</gel:log>
       </core:when>
       <core:otherwise>
        <gel:log>Login successful. Session ID is ${sessionID}</gel:log>
       </core:otherwise>
    </core:choose>

    <core:forEach items="${data.rows}" trim="true" var="row">
    <core:set value="${row.get('idea_name')}" var="idea_name"/>
    <core:set value="${row.get('idea_code')}" var="idea_code"/>
    <core:set value="${row.get('idea_start_plus_6')}" var="idea_start_plus_6"/>
    <core:set value="${row.get('idea_finish_plus_6')}" var="idea_finish_plus_6"/>

    <!-- Xog in new finish dates on Ideas -->
    <soap:invoke endpoint="${xogURL}" var="updateFinish">
    <soap:message>
      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
       <soapenv:Header>
        <xog:Auth>
         <xog:SessionID>${sessionID}</xog:SessionID>
        </xog:Auth>
       </soapenv:Header>
    <soapenv:Body>

    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_idea.xsd">
      <Header action="write" externalSource="NIKU" objectType="idea" version="8.0"/>
      <Ideas>
        <Idea name="${idea_name}" objectID="${idea_code}" openForTimeEntry="0" finish="${idea_finish_plus_6}">
        </Idea>
      </Ideas>
    </NikuDataBus>

    </soapenv:Body>
    </soapenv:Envelope>
    </soap:message>
    </soap:invoke>

    <!-- Xog in new start dates on Ideas -->
    <soap:invoke endpoint="${xogURL}" var="updateStart">
    <soap:message>
      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
       <soapenv:Header>
        <xog:Auth>
         <xog:SessionID>${sessionID}</xog:SessionID>
        </xog:Auth>
       </soapenv:Header>
    <soapenv:Body>

    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_idea.xsd">
      <Header action="write" externalSource="NIKU" objectType="idea" version="8.0"/>
      <Ideas>
        <Idea name="${idea_name}" objectID="${idea_code}" openForTimeEntry="0" start="${idea_start_plus_6}">
        </Idea>
      </Ideas>
    </NikuDataBus>

    </soapenv:Body>
    </soapenv:Envelope>
    </soap:message>
    </soap:invoke>

    </core:forEach>

    <!-- Log out of xog -->
    <soap:invoke endpoint="${xogURL}" var="logoutresult">
    <soap:message>
      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
       <soapenv:Header>
        <xog:Auth>
         <xog:SessionID>${sessionID}</xog:SessionID>
        </xog:Auth>
       </soapenv:Header>
      <soapenv:Body>
        <xog:Logout/>
      </soapenv:Body>
    </soapenv:Envelope>
    </soap:message>
    </soap:invoke>

    <!-- comment out below log output once in production  -->

    <gel:log> ${xogURL}, ${xogUsername}, ${xogPassword}, ${sessionID}</gel:log>
    </gel:script>


  • 2.  Re: Xogging date changes onto Ideas
    Best Answer

    Posted Oct 03, 2016 03:01 PM

    I suspect you are right.  Just guessing that the "Execute Process" job just hands off the process to the process engine and then ends (< 1 sec).

     

    Trying to hold to "Execute Process" job until the process engine has completed it work might be difficult.  Give that it does a fire and forget, you won't have any feedback that the process is complete.

     

    My approach to solving this (for On Premise) would be to create a new job that does the update. 

     

    I built something like this (got the idea from the link below) to deal with running command line type thingies

     

    CA Clarity Job Launcher 

     

    V/r,

    Gene



  • 3.  Re: Xogging date changes onto Ideas

    Posted Oct 03, 2016 06:44 PM

    Awesome Gene - Thanks! I'm digging into your documents tonight - I think I see the light at the end of the tunnel. I'll report back this week as I get time to dig back into this.

     

    Brilliant!



  • 4.  Re: Xogging date changes onto Ideas

    Posted Oct 04, 2016 10:15 AM

    Linking this here since this is the business problem to solve in order for this to work with the OOTB capabilities.

    Execute Process Job should wait until the process finish



  • 5.  Re: Xogging date changes onto Ideas

    Posted Oct 05, 2016 03:08 PM

    Success! 120 Ideas 'kicked out' 6 months in our Development Env. No conflicts, deadlocks or roll backs.

     

    I'm using your generic process launcher on the GEL above. 

     

    Here's a little test harness for the next developer (windows, but you get it). 

    1) Run Hello World manually on the PPM server to verify GEL Script execution (Result: hello_world.log)

    2) Create job in CA PPM with CA PPM Job Launcher. Run job with Log Out option to verify proper install of Job Launcher (Result: job launcher log with Hello World in it).

     

    GEL: hello_world.xml

    <gel:script xmlns:core="jelly:core" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary">
    <file:writeFile fileName="hello_world.log" embedded="false">
      <core:forEach indexVar='i' begin='1' end='3'>
       <gel:out>Hello World ${i}!</gel:out>
        <file:line>
         <file:column value="Hello World ${i}!"/>
        </file:line>
      </core:forEach>
    </file:writeFile>
    </gel:script>

     

    run_hello_world batch file:

    gel -script hello_world.xml

     

    Thanks!



  • 6.  Re: Xogging date changes onto Ideas

    Posted Oct 25, 2016 03:59 PM

    A BIG thanks to all here in the forums, especially these two threads:

    How to code an HTML formatted email using GEL script

    GEL:  Email to Group

    An extra-special "Thanks!" to gcubed for a little something called a GEL Launcher.

    <!-- Kick Out Ideas in Intake Due to start within 60 days -->
    <gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
      xmlns:core="jelly:core"
      xmlns:util="jelly:util"
      xmlns:email="jelly:email"
      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:jxml="jelly:xml"
      xmlns:xog="http://www.niku.com/xog"
      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


    <core:set var="debug" value="${true}"/>
    <gel:parameter var="xogUsername" default="not set"/>
    <gel:parameter var="xogPassword" secure="true" default="not set"/>
    <gel:parameter var="fromEmailAddress" default="noreply@******.com"/>
    <gel:parameter var="emailGroup" default="OSUMC Demand Managers - IT"/>

    <!-- Get the list of projects -->
    <gel:setDataSource dbId="niku"/>
    <sql:query var="data" escapeText="0">
              <![CDATA[SELECT
              I.NAME idea_name
              , I.ID idea_id
              , I.CODE idea_code
              , (SELECT CONVERT(char(10), DATEADD(mm, DATEDIFF(mm, 0, I.SCHEDULE_START) + 6, 0), 126))+'T00:00:00' idea_new_start_for_xog
              , (SELECT CONVERT(char(10), DATEADD(mm, DATEDIFF(mm, 0, I.SCHEDULE_START) + 6, 0), 101))  idea_new_start_for_email
              , (SELECT CONVERT(char(10), DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.SCHEDULE_FINISH) + 7, 0)), 126))+'T00:00:00' idea_new_finish_for_xog
              , (SELECT CONVERT(char(10), DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.SCHEDULE_FINISH) + 7, 0)), 101)) idea_new_finish_for_email
              , CONVERT(char(10), FLOOR(I.LABOR_ETCSUM/3600)) AS idea_allocation_hours
              FROM INV_INVESTMENTS I
              WHERE I.ODF_OBJECT_CODE = 'Idea'
              AND I.IS_ACTIVE = 1
              AND I.STAGE_CODE = 'osumc_intake'
              AND I.SCHEDULE_START  < (SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 2, 0)))]]>

    </sql:query>

    <!--  Get xog URL from application by fpena -->
    <core:invokeStatic className="com.niku.union.config.ConfigurationManager" method="getInstance" var="config"/>
       <core:set var="v_IsLocal">0</core:set>
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getId()}" var="WebServerid"/>
    <core:if test="${WebServerid == 'app'}">
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getAddress()}" var="AddressIp"/>
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getSslEntryUrl()}" var="EntryURL"/>
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(0).getSslPort()}" var="Port"/>
      <core:if test="${EntryURL == 'http://localhost' }">
       <gel:log level="info" message="NULL ADDRESS IS LOCAL... "/>
       <core:set var="v_IsLocal">1</core:set>
       <core:set var="AddressIp">${EntryURL}</core:set>
      </core:if>
    </core:if>
    <core:if test="${WebServerid == 'nsa'}">
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(1).getAddress()}" var="AddressIp"/>
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(1).getEntryUrl()}" var="EntryURL"/>
       <core:set value="${config.getProperties().getWebServer().getWebServerInstance(1).getPort()}" var="Port"/>
      <core:if test="${EntryURL == 'http://localhost' }">
       <gel:log level="info" message="NULL ADDRESS IS LOCAL... "/>
       <core:set var="v_IsLocal">1</core:set>
       <core:set var="AddressIp">${EntryURL}</core:set>
      </core:if>
    </core:if>
    <core:if test="${Port != null }">
      <core:set value="${EntryURL}" var="EntryURL"/>
    </core:if>
    <core:if test="${v_IsLocal == 0 }">
      <core:if test="${AddressIp != '' }">
       <core:set value="http://${AddressIp}" var="EntryURL"/>
      </core:if>
    </core:if>
       <core:set value="${config.getProperties().getDirectories().getInstallDir()}" var="NIKUHOME"/>
       <core:set value="${EntryURL}/niku/xog" var="xogURL"/>
       <core:set value="${config.getProperties().getMailServer().getHost()}" var="mailServer"/>

    <!-- Log into xog and get a session ID -->
    <soap:invoke endpoint="${xogURL}" var="auth">
      <soap:message>
       <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
         <soapenv:Header/>
          <soapenv:Body>
           <xog:Login>
            <xog:Username>${xogUsername}</xog:Username>
            <xog:Password>${xogPassword}</xog:Password>
           </xog:Login>
          </soapenv:Body>
       </soapenv:Envelope>
      </soap:message>
    </soap:invoke>

    <!-- Checking whether a sessionID is returned. If not, it means that login was unsuccessful -->
    <gel:set asString="true" select="$auth/SOAP-ENV:Envelope/SOAP-ENV:Body/xog:SessionID/text()" var="sessionID"/>
    <core:choose>
       <core:when test="${sessionID == null}">
        <gel:log>Couldn't log in. Please check the hostname/username/password.</gel:log>
       </core:when>
       <core:otherwise>
        <gel:log>Login successful. Session ID is ${sessionID}</gel:log>
       </core:otherwise>
    </core:choose>

    <core:forEach items="${data.rows}" trim="true" var="row">
    <core:set value="${row.get('idea_name')}" var="idea_name"/>
    <core:set value="${row.get('idea_code')}" var="idea_code"/>
    <core:set value="${row.get('idea_new_start_for_xog')}" var="idea_new_start_for_xog"/>
    <core:set value="${row.get('idea_new_finish_for_xog')}" var="idea_new_finish_for_xog"/>

    <!-- Xog in new finish dates on Ideas -->
    <soap:invoke endpoint="${xogURL}" var="updateFinish">
    <soap:message>
      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
       <soapenv:Header>
        <xog:Auth>
         <xog:SessionID>${sessionID}</xog:SessionID>
        </xog:Auth>
       </soapenv:Header>
    <soapenv:Body>

    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_idea.xsd">
      <Header action="write" externalSource="NIKU" objectType="idea" version="8.0"/>
      <Ideas>
        <Idea name="${idea_name}" objectID="${idea_code}" openForTimeEntry="0" finish="${idea_new_finish_for_xog}">
        </Idea>
      </Ideas>
    </NikuDataBus>

    </soapenv:Body>
    </soapenv:Envelope>
    </soap:message>
    </soap:invoke>

    <!-- Xog in new start dates on Ideas -->
    <soap:invoke endpoint="${xogURL}" var="updateStart">
    <soap:message>
      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
       <soapenv:Header>
        <xog:Auth>
         <xog:SessionID>${sessionID}</xog:SessionID>
        </xog:Auth>
       </soapenv:Header>
    <soapenv:Body>

    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_idea.xsd">
      <Header action="write" externalSource="NIKU" objectType="idea" version="8.0"/>
      <Ideas>
        <Idea name="${idea_name}" objectID="${idea_code}" openForTimeEntry="0" start="${idea_new_start_for_xog}">
        </Idea>
      </Ideas>
    </NikuDataBus>

    </soapenv:Body>
    </soapenv:Envelope>
    </soap:message>
    </soap:invoke>
    </core:forEach>

    <!-- Log out of xog -->
    <soap:invoke endpoint="${xogURL}" var="logoutresult">
    <soap:message>
      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
       <soapenv:Header>
        <xog:Auth>
         <xog:SessionID>${sessionID}</xog:SessionID>
        </xog:Auth>
       </soapenv:Header>
      <soapenv:Body>
        <xog:Logout/>
      </soapenv:Body>
    </soapenv:Envelope>
    </soap:message>
    </soap:invoke>

    <gel:parse var="v_mail_content">
      <table>
          <tr><font face="Calibri" size="3">
              <th align="center">Idea Name</th>
              <th align="center">Idea ID</th>
              <th align="center">Start Date</th>
              <th align="center">Finish Date</th>
              <th align="center">Allocation Hours</th>
          </font></tr>
      </table>
    </gel:parse>

    <core:forEach items="${data.rows}" trim="true" var="row">
    <core:set value="${row.get('idea_name')}" var="idea_name"/>
    <core:set value="${row.get('idea_id')}" var="idea_id"/>
    <core:set value="${EntryURL}/niku/nu#action:pma.ideaProperties&amp;id=${idea_id}" var="v_inv_url"/>
    <core:set value="${row.get('idea_code')}" var="idea_code"/>
    <core:set value="${row.get('idea_new_start_for_email')}" var="idea_new_start_for_email"/>
    <core:set value="${row.get('idea_new_finish_for_email')}" var="idea_new_finish_for_email"/>
    <core:set value="${row.get('idea_allocation_hours')}" var="idea_allocation_hours"/>
      
    <gel:parse var="v_detail_item">
      <tr><font face="Calibri" size="3">
          <td align="left"><a href="${v_inv_url}">${row.idea_name}</a></td>
          <td align="center">${row.idea_code}</td>
          <td align="center">${row.idea_new_start_for_email}</td>
          <td align="center">${row.idea_new_finish_for_email}</td>
          <td align="center">${row.idea_allocation_hours}</td>
      </font></tr>
    </gel:parse>

    <core:catch var="gel_error">
        <gel:set select="$v_mail_content/table" value="${v_detail_item}" insert="true" trim="false"/>
    </core:catch>

    </core:forEach>

    <core:catch var="v_exception">
         <gel:set asString="true" select="$v_mail_content/table" var="v_variable"/>
    </core:catch>   

    <!--Set email address count:-->
    <core:set value="0" var="emailCount"/>

    <!--e_group used to loop through group specified in emailGroup and create email with all addresses-->
    <sql:query var="e_group">
    <![CDATA[SELECT u.email_address
    FROM CMN_CAPTIONS_NLS NLS,
    cmn_sec_users u,
    CMN_SEC_GROUPS G,
    cmn_sec_user_groups UG,
    CMN_LOOKUPS L,
    srm_resources r
    WHERE NLS.TABLE_NAME = 'CMN_SEC_GROUPS'
    AND NLS.PK_ID = G.ID
    AND NLS.LANGUAGE_CODE = 'en'
    AND u.id = ug.user_id
    AND u.id = r.user_id
    AND G.GROUP_TYPE_ID = L.ID
    and g.id = ug.group_id
    AND L.LOOKUP_TYPE = 'SEC_GROUP_TYPE'
    AND g.is_active = '1'
    AND r.is_active = '1'
    AND NLS.name = '${emailGroup}']]>

    </sql:query>

    <!--count_e_group used to count active members of group specified in emailGroup and create email with all addresses-->
    <sql:query var="count_e_group">
    <![CDATA[SELECT count(*) as num
    FROM CMN_CAPTIONS_NLS NLS,
    cmn_sec_users u,
    CMN_SEC_GROUPS G,
    cmn_sec_user_groups UG,
    CMN_LOOKUPS L,
    srm_resources r
    WHERE NLS.TABLE_NAME = 'CMN_SEC_GROUPS'
    AND NLS.PK_ID = G.ID
    AND NLS.LANGUAGE_CODE = 'en'
    AND u.id = ug.user_id
    AND u.id = r.user_id
    AND G.GROUP_TYPE_ID = L.ID
    and g.id = ug.group_id
    AND L.LOOKUP_TYPE = 'SEC_GROUP_TYPE'
    AND g.is_active = '1'
    AND r.is_active = '1'
    AND NLS.name = '${emailGroup}']]>

    </sql:query>

    <!--Loop through e_group (note 3 tests: >0 >1 =0 -->
    <core:if test="${count_e_group.rows[0].num == 1}">
    <core:set value="${e_group.rows[0].email_address}" var="email_addr"/>
    </core:if>

    <core:if test="${count_e_group.rows[0].num > 1}">
    <!--Write first record-->
    <core:set value="${e_group.rows[0].email_address}" var="email_addr"/>

    <!--Write each subsequent record with a semi colon-->
    <core:forEach begin="1" items="${e_group.rows}" trim="true" var="email_row">
    <core:set value="${email_addr},${email_row.email_address}" var="email_addr"/>
    </core:forEach>
    </core:if>

    <core:if test="${count_e_group.rows[0].num == 0}">
    <core:set value="${fromEmailAddress}" var="email_addr"/>
    </core:if>

    <!-- Set MailAgent -->
    <core:invokeStatic className="java.lang.Class" method="forName" var="recipientType">
    <core:arg type="java.lang.String" value="javax.mail.Message$RecipientType" />
    </core:invokeStatic>
    <core:set value="${recipientType.getField('TO')}" var="recipientTypeTO" />
    <core:set value="${recipientTypeTO.TO}" var="recipientTypeTO" />
    <core:set value="${recipientType.getField('CC')}" var="recipientTypeCC" />
    <core:set value="${recipientTypeCC.CC}" var="recipientTypeCC" />
    <core:set value="${recipientType.getField('BCC')}" var="recipientTypeBCC" />
    <core:set value="${recipientTypeBCC.BCC}" var="recipientTypeBCC" />

    <!-- Initiate a new java mail session -->
    <core:new className="java.util.Properties" var="props" />
    <core:set value="${props.put('mail.transport.protocol', 'smtp')}" var="void" />
    <core:set value="${props.put('mail.smtp.host', mailServer)}" var="void" />
    <core:set value="${props.put('mail.smtp.auth', 'true')}" var="void" />
    <core:new className="com.niku.union.notification.MailAuthenticator" var="ppm_auth" />
    <core:invokeStatic className="javax.mail.Session" method="getInstance" var="mailSession">
    <core:arg type="java.util.Properties" value="${props}" />
    <core:arg type="javax.mail.Authenticator" value="${ppm_auth}" />
    </core:invokeStatic>

    <!-- HTML email message -->
    <core:choose>
       <core:when test="${idea_name == null}">
    <gel:parse var="htmlMessage">
    <message>
         <![CDATA[<html>
                <head>
                <style>
                table, th, td {
                    border: 1px solid black;
                    border-collapse: collapse;
                }
                th, td {
                    padding: 5px;
                }
                th {
                    text-align: left;
                }
                </style>
                </head>
                <body>
                <font face="Calibri" size="3">
                <p>No Ideas have been found in Intake with a Start Date less than 60 days out.</p>
                </br></font></body></html>]]>

    </message>
    </gel:parse>
    </core:when>
    <core:otherwise>
    <gel:parse var="htmlMessage">
    <message>
         <![CDATA[<html>
                <head>
                <style>
                table, th, td {
                    border: 1px solid black;
                    border-collapse: collapse;
                }
                th, td {
                    padding: 5px;
                }
                th {
                    text-align: left;
                }
                </style>
                </head>
                <body>
                <font face="Calibri" size="3">
                <p>The following Ideas in Intake were found to start within 60 days and have been kicked back 6 months:</p>
                ]]>

                ${v_variable}
                <![CDATA[</br>]]>
                Please contact <![CDATA[<a href="mailto:itpmo@osumc.edu">IT PMO Demand Management</a>]]> with any questions.
                <![CDATA[</br></font></body></html>]]>
    </message>
    </gel:parse>
    </core:otherwise>
    </core:choose>

    <gel:forEach select="$htmlMessage/message/node()" var="thisMessageNode">
      <gel:set asString="true" select="$thisMessageNode" var="thisMessageNodeText" />
      <core:set value="${thisMessageText}${thisMessageNodeText}" var="thisMessageText" />
    </gel:forEach>

           
    <!-- Add sender -->
    <core:new className="javax.mail.internet.InternetAddress" var="sender">
      <core:arg type="java.lang.String" value="${fromEmailAddress}" />
    </core:new>                            

    <!-- Add recipients -->
    <core:new className="javax.mail.internet.InternetAddress" var="internetAddress"/>
      <core:set value="${email_addr}" var="recipientsTO" />
      <core:set value="${fromEmailAddress}" var="recipientsCC" />                           

    <!-- Add subject -->
      <core:set value="Intake Queue Kick-The-Can-O-Matic" var="subject" />

    <!-- Add email body text and format -->
      <core:new className="javax.mail.internet.MimeMultipart" var="multiPart" />
      <core:new className="javax.mail.internet.MimeBodyPart" var="bodyPart" />
      <core:set value="${bodyPart.setContent(thisMessageText, 'text/html')}" var="void" />
      <core:set value="${multiPart.addBodyPart(bodyPart)}" var="void" />
          
    <!-- Send email message -->
    <core:new className="javax.mail.internet.MimeMessage" var="message">
    <core:arg type="javax.mail.Session" value="${mailSession}" />
    </core:new>
    <core:set value="${mailSession.setDebug(debug)}" var="void" />
    <core:set value="${message.setFrom(sender)}" var="void" />
    <core:choose>
    <core:when test="${recipientsTO.indexOf(',') > 0}">
    <core:set value="${message.setRecipients(recipientTypeTO, internetAddress.parse(recipientsTO))}" var="void" />
    </core:when>
    <core:otherwise>
    <core:new className="javax.mail.internet.InternetAddress" var="recipientsTO">
    <core:arg type="java.lang.String" value="${recipientsTO}" />
    </core:new>
    <core:set value="${message.setRecipient(recipientTypeTO, recipientsTO)}" var="void" />
    </core:otherwise>
    </core:choose>
    <core:choose>
    <core:when test="${recipientsCC.indexOf(',') > 0}">
    <core:set value="${message.setRecipients(recipientTypeCC, internetAddress.parse(recipientsCC))}" var="void" />
    </core:when>
    <core:otherwise>
    <core:new className="javax.mail.internet.InternetAddress" var="recipientsCC">
    <core:arg type="java.lang.String" value="${recipientsCC}" />
    </core:new>
    <core:set value="${message.setRecipient(recipientTypeCC, recipientsCC)}" var="void" />
    </core:otherwise>
    </core:choose>
    <core:set value="${message.setSubject(subject)}" var="void" />
    <core:set value="${message.setContent(multiPart)}" var="void" />
    <core:set value="${mailSession.getTransport()}" var="transport" />
    <core:set value="${transport.connect()}" var="void" />
    <core:switch on="${transport.isConnected()}">
    <core:case value="${true}">
    <core:set value="${transport.send(message)}" var="void" />
    <core:set value="${transport.close()}" var="void" />
    </core:case>
    <core:default>
    <core:new className="java.lang.Exception" var="exception">
    <gel:log level="ERROR">"Cannot connect to mail server: ${MailServer}"</gel:log>
    </core:new>
    </core:default>
    </core:switch>

    <!-- comment out below log output once in production  -->
    <gel:log> ${xogURL}, ${xogUsername}, ${xogPassword}, ${sessionID}</gel:log>
    </gel:script>

    Thanks all!