Gel script : auto-populate a date field

[Legacy User]

Hello Everyone,

I'm doing a Gel script that allow me to auto populate a date field with date system when the condition in my process is true but i get stuck. i'm doing something like this:

 

<!-- GET sysdate  -->
    <core:catch var="exception">
        <sql:query var="req">
<![CDATA[       
   SELECT I.NAME ISSUE_NAME 
        , I.RIM_RISK_ISSUE_CODE ISSUE_CODE 
        , P.CODE PROJECT_CODE 
        , R.UNIQUE_NAME AS ISSUE_ASSIGNED_TO 
        ,  CAST(sysdate AS DATE) AS affectationDate
        , A.x_closed_date
        FROM RIM_RISKS_AND_ISSUES I 
        JOIN INV_INVESTMENTS P ON P.ID = I.PK_ID 
        JOIN ODF_CA_ISSUE A ON A.ID = I.ID 
        JOIN SRM_RESOURCES R ON R.USER_ID = I.ASSIGNED_TO 
        WHERE I.TYPE_CODE = 'ISSUE'
        AND I.ID = ${gel_objectInstanceId}
       ]]>
       </sql:query>
    </core:catch>
    <core:choose>
        <core:when test="${exception != null}">
            <gel:log level="ERROR" message="[-] SQL error"/>
            <core:set value="1" var="ProcessError"/>
            <core:if test="${DebugLevel ge 1}">
                <gel:log category="SQL" level="ERROR" message="${exception}"/>
            </core:if>
        </core:when>
        <core:otherwise>
            <core:if test="${req.rowCount !=0}">
                <core:forEach items="${req.rowsByIndex}" trim="true" var="rowParam">
                    <core:if test="${rowParam[0] != null}">
                    <core:set value="${rowParam[4]}" var="affectationDate"/>
                    <core:set value="${rowParam[5]}" var="x_closed_date"/>
                        <core:set value="${affectationDate}" var="x_closed_date"/>
                        <ColumnValue name="affectationDate">${x_closed_date}</ColumnValue>
                        <gel:log category="DATA" level="info" message="traitement of data from ${affectationDate}"/>
                    </core:if>
                </core:forEach>
            </core:if>
        </core:otherwise>
    </core:choose>

[David Morton]

and how are you stuck?

 

(the code you have posted is only running a SELECT statement and populating some GEL variables ; I would expect it then needed to update data back into the application so you need some more code that constructs a XOG call to update the application)

[Legacy User]

Did i need to use a Update query that set "x_closed_date = sysdate"? because its undesirable to do that in Data bas...

 

This is the rest of my script :

 

<!-- Xog current dateSYS Into ClosedDate  --> 
      <soap:invoke endpoint="${xogURL}" var="runresult"> 
        <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_issue.xsd">  
                <Header action="write" externalSource="NIKU" objectType="issue" version="13.3.0.286"/> 
                <issues> 
                  <issue code="${data.rows.0.issue_code}" name="${data.rows.0.issue_name}" ownerCode="${data.rows.0.issue_assigned_to}" projectCode="${data.rows.0.project_code}"> 
                    <CustomInformation> 
                      <ColumnValue name="closed_date">${data.rows.0.affectationDate}</ColumnValue> 
                    </CustomInformation> 
                  </issue> 
                </issues> 
              </NikuDataBus> 
            </soapenv:Body> 
          </soapenv:Envelope> 
        </soap:message> 
      </soap:invoke> 

                            <!-- SEND XOG -->
                            <core:if test="${logLevel ge 3}">
                                <gel:log category="XOG" level="info" message="Sending LOG XOG"/>
                            </core:if>
                            <core:catch var="exception">
                                <soap:invoke endpoint="${hostClarity}/niku/xog" var="output">
                                    <soap:message>
                                        <soapenv:Envelope>
                                            <soapenv:Header>
                                                <xog:Auth>
                                                    <xog:SessionID>${sessionID}</xog:SessionID>
                                                </xog:Auth>
                                            </soapenv:Header>
                                            <soapenv:Body>
                                                <gel:include select="$LOG_XOG"/>
                                            </soapenv:Body>
                                        </soapenv:Envelope>
                                    </soap:message>
                                </soap:invoke>
                            </core:catch>
                            <core:choose>
                                <core:when test="${exception != null}">
                                    <gel:log level="info" message="[-] Error while sending LOG XOG"/>
                                    <core:if test="${logLevel ge 1}">
                                        <gel:log category="SOAP" level="info" message="${exception}"/>
                                    </core:if>
                                </core:when>
                                <core:otherwise>
                                    <core:if test="${logLevel ge 3}">
                                        <gel:log category="XOG" level="info" message="LOG XOG sent successfully"/>
                                    </core:if>
                                    <gel:set asString="true" select="$output//XOGOutput/ErrorInformation/Exception/text()" var="DocErrTxt1"/>
                                    <gel:set asString="true" select="$output//XOGOutput/Records/Record/ErrorInformation/Exception/text()" var="DocErrTxt2"/>
                                    <gel:set asString="true" select="$output//XOGOutput/Records/Record/ErrorInformation/Description/text()" var="DocErrTxt3"/>
                                    <gel:set asString="true" select="$output//XOGOutput/Records/Record/ErrorInformation/Severity/text()" var="Error_type"/>
                                    <gel:set asString="true" select="$output" var="docOut"/>
                                    <gel:set asString="true" select="$LOG_XOG" var="xogIn"/>
                                    <core:if test="${logLevel ge 6}">
                                        <gel:log category="XOG" level="info" message="${xogIn}"/>
                                    </core:if>
                                </core:otherwise>
                            </core:choose>
                            <core:choose>
                                <core:when test="${DocErrTxt1 != null || DocErrTxt2 != null || ( DocErrTxt3 != null and Error_type != 'WARNING') }">
                                    <gel:log level="info" message="[-] error while including LOG XOG"/>
                                    <core:if test="${logLevel ge 3}">
                                        <core:if test="${DocErrTxt1 != null}">
                                            <gel:log category="XOG" level="info" message="${DocErrTxt1}"/>
                                        </core:if>
                                        <core:if test="${DocErrTxt2 != null}">
                                            <gel:log category="XOG" level="info" message="${DocErrTxt2}"/>
                                        </core:if>
                                        <core:if test="${DocErrTxt3 != null}">
                                            <gel:log category="XOG" level="info" message="${DocErrTxt3}"/>
                                        </core:if>
                                    </core:if>
                                </core:when>
                                <core:otherwise>
                                    <core:if test="${logLevel ge 3}">
                                        <core:if test="${DocErrTxt3 != null and Error_type == 'WARNING'}">
                                            <core:if test="${logLevel ge 3}">
                                                <gel:log category="XOG" level="info" message="${DocErrTxt3}"/>
                                            </core:if>
                                        </core:if>
                                        <gel:log category="XOG" level="info" message="LOG XOG included successfully"/>
                                        <gel:log category="XOG" level="info" message="${docOut}"/>
                                    </core:if>
                                </core:otherwise>
                            </core:choose>
                            <!-- End of custom script -->
                        </core:otherwise>
                    </core:choose>
                </core:otherwise>
            </core:choose>
            <core:if test="${logLevel ge 3}">
                <gel:log category="XOG" level="info" message="Disconnecting"/>
            </core:if>
            <core:catch var="exception">
                <soap:invoke endpoint="${hostClarity}/niku/xog" var="result">
                    <soap:message>
                        <soapenv:Envelope>
                            <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>
            </core:catch>
            <core:choose>
                <core:when test="${exception != null}">
                    <gel:log level="error" message="[-] Error while disconnecting from ${hostClarity}."/>
                    <core:set value="1" var="errorFlag"/>
                    <core:if test="${logLevel ge 1}">
                        <gel:log category="SOAP" level="info" message="${exception}"/>
                    </core:if>
                </core:when>
                <core:otherwise>
                    <core:if test="${logLevel ge 3}">
                        <gel:log category="XOG" level="info" message="Disconnected"/>
                    </core:if>
                </core:otherwise>
            </core:choose>
            <core:if test="${logLevel ge 0}">
                <core:choose>
                    <core:when test="${errorFlag == 1}">
                        <gel:log category="PROC" level="error" message="Finished with errors"/>
                    </core:when>
                    <core:otherwise>
                        <gel:log category="PROC" level="info" message="Finished without errors"/>
                    </core:otherwise>
                </core:choose>
            </core:if>
        </gel:script>
    </scriptText>
    <scriptParameter isSecure="false" name="logLevel" value="10"/>
</customScript>

[David Morton]

I'm not really sure what you are trying to do with all that XOG "log" stuff - the XOG (upto line 25 above) should be doing the update to the application.

 

You still have not said what you are "stuck" with though?  -  There is no way I am going to debug all that code for you though from here - what I would suggest is doing the development of this bit-by-bit, get one bit working and then move onto the next bit, if you are then "stuck" you are stuck with a more specific problem that someone might have an answer for.

 

--

 

Generally direct SQL update in GEL is regarded as "bad" (there is nothing to stop you from doing it though yet - I have heard the odd comment that it might stop working in later versions of the application though) ; its "bad" because CA will not support you doing it (not let you do it in SAAS, not work to any SLA with any errors that might happen because of it) - but if the update is to a custom-field then there is normally little danger in doing it, but "good practice" would say that always doing updates through XOG is the right way to do it.