AnsweredAssumed Answered

Help Updating a GEL Script

Question asked by mtognetti on Jun 7, 2018
Latest reply on Jul 5, 2018 by urmas

Good Day Community,

 

I still struggle with XOG/Gel Script processes. This is due to not having to spend enough time in this area to become comfortable.  As a result I have a couple of questions concerning a Gel Script that we have been using for many years which needs a slight (one additional data field) update.

 

  • We have determined that we need to add an element to the XOG File that is created by the below GEL Script.       The element is an employee number (custom to our organization) and we have created a new data element in PPM for it. The new element lives on the niku.ODF_CA_Resource table.
  • The GEL script creates a XOG file that loads data elements to the Resource table. You can see that at lines 29 and 65 I have accounted for this new data field. However, in the definition at row 72 (PersonalInformation) I am sure that I need to define the element? My questions are
    • What do I call the element? Would it be userEmpNum=”${EmployeeNumber}”
    • Do I have to account for the fact that it is a custom field in some way?

I appreciate any help here and will continue to learn more about GEL and XOG in the hope that I will be more able to be an active participant here.

 

Thank you.

 

---Gel Script

<?xml version="1.0" encoding="UTF-8"?>

<gel:script

  xmlns:core="jelly:core"

  xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"

  xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"

  xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/"

  xmlns:sql="jelly:sql"

  xmlns:xog="http://www.niku.com/xog"

  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 

  <!--Set Script Variables-->

  <gel:parameter var="XOGURL" />

  <gel:parameter var="XOGUsername" />

  <gel:parameter var="XOGPassword" />

  <gel:formatDate format="yyyy-MM-dd HHmmss" stringVar="date" />

 

  <!--Set Clarity Datasource-->

  <gel:setDataSource dbId="Niku" var="ClarityDB"/>

 

  <!--Run SQL Query - Get Data-->

  <!--Get New Users from the PH defined Employee Table-->

  <sql:query var="resource" dataSource="${ClarityDB}">

                                SELECT

                                ou.OrgUserID,

                                e.UserName,

                                ou.LastName,

                                ou.FirstName,

                                ou.Email,

                                EmployeeNumber --added 6/6/2018

                                                               

                                FROM NIKU.Z_EMPLOYEE e

                                JOIN NIKU.Z_OrgUser ou on e.OrgUserID = ou.OrgUserID

                               

                                WHERE

                                e.TermCode = ''

                                AND ou.email not in ('null', '')

                                AND ou.email is not null

                                AND NOT EXISTS (SELECT 1 FROM NIKU.SRM_RESOURCES WHERE UNIQUE_NAME = e.USERNAME)

                                AND NOT EXISTS (SELECT 1 FROM niku.Z_OrgUserName T1

                                                JOIN niku.SRM_RESOURCES T2 ON T1.UserName = T2.UNIQUE_NAME

                                                WHERE e.OrgUserID = T1.OrgUserID AND T2.UNIQUE_NAME != e.UserName)

                               

                                ORDER BY e.UserName

  </sql:query>

 

  <!--Check if query returned records-->

  <core:choose>

    <!-- If records are returned, continue with resource import  -->

    <core:when test="${resource.rows[0].OrgUserID != null}">

 

      <gel:parse var="xogBody">

        <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

                     xsi:noNamespaceSchemaLocation="../../xsd/nikuxog_user.xsd" >

          <Header action="write"

                  externalSource="NIKU"

                  objectType="user"

                  version="8.1.1.4445" />

          <Users>

            <core:forEach var="row" items="${resource.rowsByIndex}">

              <core:set var="OrgUserID" value="${row[i+0]}" />

              <core:set var="UserName" value="${row[i+1]}" />

              <core:set var="LastName" value="${row[i+2]}" />

              <core:set var="FirstName" value="${row[i+3]}" />

              <core:set var="Email" value="${row[i+4]}" />

              <core:set var="EmployeeNumber" value="${row(i+5}" /> <!--added 6/6/2018 -->

              <User externalId="${OrgUserID}"

                  userLanguage="English"

                  userLocale="en_US"

                  userName="${UserName}"

                  userStatus="LOCK"

                  userTimezone="America/Los_Angeles"

                  userType="INTERNAL">

                <PersonalInformation emailAddress="${Email}"

                                    firstName="${FirstName}"

                                     lastName="${LastName}" />

                <Resource resourceId="${UserName}" />

              </User>

            </core:forEach>

          </Users>

        </NikuDataBus>

      </gel:parse>

 

      <soap:invoke endpoint="${XOGURL}/niku/xog" var="auth">

        <soap:message>

          <soap-env:Envelope

            xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/"

            xmlns:xog="http://www.niku.com/xog">

            <soap-env:Header/>

            <soap-env:Body>

              <xog:Login>

                <xog:Username>${XOGUsername}</xog:Username>

                <xog:Password>${XOGPassword}</xog:Password>

              </xog:Login>

            </soap-env:Body>

          </soap-env:Envelope>

        </soap:message>

      </soap:invoke>

 

      <!-- Checking whether a sessionID is returned.  If not, it means that Login was not successful. -->

      <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 level="error" message="Cannot login to Clarity XOG. Check username and password."/>

        </core:when>

        <core:otherwise>

 

          <!-- Send a XOG request. -->

          <soap:invoke endpoint="${XOGURL}/niku/xog" var="result">

            <soap:message>

              <soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">

                <soap-env:Header>

                  <xog:Auth>

                    <xog:SessionID>${sessionID}</xog:SessionID>

                  </xog:Auth>

                </soap-env:Header>

                <soap-env:Body>

                  <gel:include select="$xogBody"/>

                </soap-env:Body>

              </soap-env:Envelope>

            </soap:message>

          </soap:invoke>

 

          <!--Save the Output as a Log File-->

          <gel:set select="$result/soap-env:Envelope/soap-env:Body/XOGOutput" var="XogOutput" />

          <gel:serialize fileName="D:\Clarity-PeaceHealth-Logs\Level1\${date} Resource-Output.xml" var="${XogOutput}"/>

 

          <core:choose>

            <!-- If the XOG request failed, log the error.  -->

            <core:when test="${error == 'FAILURE'}">

              <gel:log level="error" message="An error occurred during the XOG step."/>

            </core:when>

            <!-- If no error detected, log the result. -->

            <core:otherwise>

              <gel:serialize fileName="D:\Clarity-PeaceHealth-Logs\Level1\${date} Resource-Write.xml" var="${xogBody}"/>

            </core:otherwise>

          </core:choose>

          <!-- Logout XOG -->

       <soap:invoke endpoint="${XOGURL}/niku/xog" var="result">

            <soap:message>

              <soap-env:Envelope xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">

                <soap-env:Header>

                 <xog:Auth>

                    <xog:SessionID>${sessionID}</xog:SessionID>

                  </xog:Auth>

                </soap-env:Header>

                <soap-env:Body>

                  <xog:Logout/>

                </soap-env:Body>

           </soap-env:Envelope>

            </soap:message>

          </soap:invoke>

        </core:otherwise>

      </core:choose>

    </core:when>

   

    <!-- If no records are found, log a note in the process step. -->

    <core:otherwise>

      <gel:log level="info" message="No records were returned."/>

    </core:otherwise>

  </core:choose>

 

</gel:script>

 

 

--End Gel Script

 

Michael

Outcomes