I've re-used a project xog script for the idea object, to update a multi valued lookup field against the Idea that the process runs against. The sql in the script returns what is currently listed int he field + an additional value. It works fine against projects. The xog script runs without error but doesn't update the field, am I doing something wrong?
The script:
<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="jelly:sql"
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:new className="java.util.GregorianCalendar" var="starttime"/>
<gel:parameter var="XOGUsername" default="admin"/>
<gel:parameter var="XOGPassword" default="clarity" secure="true"/>
<!--<gel:parameter var="XOGURL" default="localhost"/>-->
<gel:parse file="../config/properties.xml" var="properties"/>
<gel:set asString="true" select="$properties/properties/webServer/@schedulerUrl" var="XOGURL"/>
<core:set var="clarity_url">${XOGURL}/niku/xog</core:set>
<gel:log level="INFO">XOG URL: ${clarity_url}</gel:log>
<gel:setDataSource dbId="niku"/>
<sql:query var="vParentFields" escapeText="false">
select inv.name,
inv.code
from niku.inv_investments inv
where inv.id=${gel_objectInstanceId}
and inv.id in (select distinct look.pk_id pk from niku.odf_multi_valued_lookups look
where look.attribute='o2_xyz' and look.object = 'idea' group by look.pk_id)
</sql:query>
<core:forEach items="${vParentFields.rowsByIndex}" var="row">
<core:set var="vs_PRNAME">${row[0]}</core:set>
<core:set var="vs_PRCODE">${row[1]}</core:set>
</core:forEach>
<gel:parse var="v_idea">
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_idea.xsd">
<Header action="write" externalSource="NIKU" objectType="idea" version="12.1.0.5840"/>
<Ideas>
<Idea name="${vs_PRNAME}" ideaID="${vs_PRCODE}">
<CustomInformation>
<ColumnValue name="o2_xyz">
</ColumnValue>
</CustomInformation>
</Idea>
</Ideas>
</NikuDataBus>
</gel:parse>
<sql:query var="multi" escapeText="false">
SELECT DISTINCT l.lookup_code id, l.name
FROM niku.cmn_lookups_v l
WHERE l.lookup_type= 'O2_ABC'
AND l.language_code='en'
and (
l.lookup_code = 'o2_jkl'
)
GROUP BY l.name, l.lookup_code
union
SELECT DISTINCT look.value id, l.name
FROM niku.inv_investments inv join
niku.odf_multi_valued_lookups look on inv.id=look.pk_id join
niku.cmn_lookups_v l on l.lookup_code = look.value
WHERE
look.attribute = 'o2_xyz'
and inv.id = ${gel_objectInstanceId}
and L.IS_ACTIVE = 1
and look.object = 'idea'
and l.LANGUAGE_CODE='en' and l.LOOKUP_TYPE = 'O2_ABC'
GROUP BY l.name, look.value
</sql:query>
<core:forEach items="${multi.rowsByIndex}" var="rowmv">
<core:set var="mv_VALUE">${rowmv[0]}</core:set>
<gel:parse var="indata">
<Value>${mv_VALUE}</Value>
</gel:parse>
<gel:set insert="true" select="$v_idea/NikuDataBus/Ideas/Idea/CustomInformation/ColumnValue[@name='o2_xyz']" value="${indata}"/>
</core:forEach>
<gel:out>[XOG]Logging into Clarity XOG at ${XOGURL}.</gel:out>
<gel:log level="INFO" category="XOG">Logging into Clarity XOG at ${XOGURL}.</gel:log>
<core:catch var="v_xog_exception">
<soap:invoke endpoint="${XOGURL}/niku/xog" 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>
<gel:set asString="true" select="$auth/soapenv:Envelope/soapenv:Body/xog:SessionID/text()" var="sessionID"/>
<core:choose>
<core:when test="${sessionID == null}">
<gel:out>[XOG]Couldn't Log in to XOG. Check the username/password and Application has started.</gel:out>
<gel:log level="ERROR" category="XOG">Couldn't Log in to XOG. Check the username/password and Application has started.</gel:log>
</core:when>
<core:otherwise>
<gel:out>[XOG]Log in successful ${sessionID}</gel:out>
<gel:log level="INFO" category="XOG">[XOG]Log in successful ${sessionID}</gel:log>
<core:catch var="v_xog_exception">
<soap:invoke endpoint="${XOGURL}/niku/xog" 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>
<gel:include select="$v_idea/NikuDataBus"/>
<gel:log>XML for build a product = <gel:expr select="$v_idea"/></gel:log>
<gel:out>XML for build a product = <gel:expr select="$v_idea"/></gel:out>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
</core:catch>
<core:choose>
<core:when test="${v_xog_exception != null}">
<gel:out>[XOG]XOG operation failed: ${v_xog_exception}</gel:out>
<gel:log level="ERROR" category="XOG">XOG operation failed: ${v_xog_exception}</gel:log>
</core:when>
<core:otherwise>
<gel:set select="$runresult/soapenv:Envelope/soapenv:Body/XOGOutput" var="writeOutputRoot"/>
<gel:set asString="true" select="$writeOutputRoot/Status/@state" var="XOGoutcome"/>
<gel:set asString="true" select="$writeOutputRoot/Records/Record" var="XOGoutputrecords"/>
<gel:set asString="true" select="$writeOutputRoot/Statistics/@totalNumberOfRecords" var="XOGtotalrecords"/>
<gel:set asString="true" select="$writeOutputRoot/Statistics/@insertedRecords" var="XOGinsertedrecords"/>
<gel:set asString="true" select="$writeOutputRoot/Statistics/@updatedRecords" var="XOGupdatedrecords"/>
<gel:set asString="true" select="$writeOutputRoot/Statistics/@failureRecords" var="XOGfailurerecords"/>
<gel:set asString="true" select="$writeOutputRoot/Records/Record/ErrorInformation/Description" var="XOGErrorDesc"/>
<gel:out>[XOG]XOG operation completed [Total ${XOGtotalrecords}, Inserted ${XOGinsertedrecords}, Updated ${XOGupdatedrecords}, Failed ${XOGfailurerecords}</gel:out>
<gel:log level="INFO" category="XOG">XOG operation completed [Total ${XOGtotalrecords}, Inserted ${XOGinsertedrecords}, Updated ${XOGupdatedrecords}, Failed ${XOGfailurerecords}]</gel:log>
<gel:out>Error Information:${XOGErrorDesc}</gel:out>
<gel:log level="INFO" category="XOG">Error Information:${XOGErrorDesc}</gel:log>
</core:otherwise>
</core:choose>
<soap:invoke endpoint="${XOGURL}/niku/xog" 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:out>[XOG]Logged out </gel:out>
<gel:log level="INFO" category="XOG">Logged out </gel:log>
</core:otherwise>
</core:choose>
</core:catch>
<core:if test="${v_xog_exception != null}">
<gel:out>XOG failed: ${v_xog_exception}</gel:out>
<gel:log level="ERROR" category="XOG">XOG failed: ${v_xog_exception}</gel:log>
</core:if>
<core:new className="java.util.GregorianCalendar" var="endtime" />
<gel:log level="INFO">Time taken: ${(endtime.getTimeInMillis() - starttime.getTimeInMillis())/1000} seconds</gel:log>
</gel:script>##