Clarity

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

In between other work, I have been reading, here in the community, as much as I can find relating to my question.

I believe it may be as simple as adding the following:

                <CustomInformation 
                 <CustomValue name="ph_empnum1" </CustomValue>
                   ph_empnum1 = "${EmployeeNumber}
                </CustomInformation>
                <Resource resourceId="${UserName}" />

In this potential solution, I use the actual column name in ODF_CA_Resource as the CustomValue name then set it to the variable.

This would follow the <user></user> segment in the <core:foreach> . 

Is this correct?

Thank you.

If the field is in ODF_CA_RESOURCES table the you should use rsm_resourse-write.xml format not cmn_sec_users_write.xml.

I do not think the CMS_SEC_USERS supports custom fields.

Hi mtognetti,

any custom field will be available in the rsm_resource-write.xml in the manner that you mentioned:

<CustomInformation  
                 <CustomValue name="ph_empnum1" </CustomValue>
                   ph_empnum1 = "${EmployeeNumber}
                </CustomInformation>

If you want to update the user and resource both, you can use two XOGs in the same loop updating different information for user and resource. For making the perfect XOG, it would be better that you take rsm_resource-read for a resource with the custom field available in that resource. Then, only keep the fields that you want to update. This way the current functioning will not be impacted.

Good day,

First, thank you to both URMAS and Kritika for their replies.  I have been away on other projects but I am back to work on this one.

Based on Kritika's response, I downloaded a current version of XOG from our Development Environment where the new field is currently implemented.  However, in the rsm_resources_write.xml (the custom information segment) I do not see the new field defined.

      -<CustomInformation>
          <ColumnValue name="RESOURCE_MENTOR">testuser</ColumnValue>
          <ColumnValue name="WILLING_TRAVEL">1</ColumnValue>
          <ColumnValue name="RESOURCE_INDUSTRY">Firmware</ColumnValue>
           <ColumnValue name="RESOURCE_GRADE">Silver</ColumnValue>
       </CustomInformation>

Is it possible that I am missing a step between the custom attribute's creation and pushing out the XOG files?

Also, because this field lives in a table separate from CMN_SEC_Users and SRM_Resources, I have thought to just write a sql stored procedure that will update the field.  Would this be a "real" problem?

Thank you.

I do not think it works.xml that way.

When you download the XOG client you get the OOTB clients and fields.

So the rsm_resources_write.xml is just a sample.

To get your custom fields read out first a resource from your system. Then that output file should have the custom fields of your system.