Problem with GEL/XOG in process

[ROBBIE MUIR]

I have the below contained within a process and it will not update the field in Idea.  We have a similar version for projects which does work sucessfully.
When checking the logs afterwards It does not generate the Idea ID and Name.  This is obviously the reason but I cannot find the cause within the script.  
Anyone have any ideas?....

<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.code idea_code,
inv.name idea_name
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_area' and look.object = 'idea' group by look.pk_id)
</sql:query>
<core:forEach items="${vParentFields.rowsByIndex}" var="row">
<core:set var="idea_code">${row[0]}</core:set>
<core:set var="idea_name">${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 objectID="${idea_code}" name="${idea_name}">
<CustomInformation>
<ColumnValue name="o2_area">
</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_PROJ_APP_AREA'
AND l.language_code='en'
and (
l.lookup_code = 'o2_s19' or
l.lookup_code = 'o2_s20' or
l.lookup_code = 'o2_s21' or
l.lookup_code = 'o2_s22' or
l.lookup_code = 'o2_s23' or
l.lookup_code = 'o2_s24' or
l.lookup_code = 'o2_s25' or
l.lookup_code = 'o2_s26' or
l.lookup_code = 'o2_s27'
)
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_area'
and inv.id = ${gel_objectInstanceId}
and L.IS_ACTIVE = 1
and look.object = 'idea'
and l.LANGUAGE_CODE='en' and l.LOOKUP_TYPE = 'O2_PROJ_APP_AREA'
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_area']" 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>

The resulting log entry looks like this and shows the Idea ID and Name missing......

ERROR 2020-01-16 15:07:39,763 [Custom script execution pool-11-thread-12] utilities.BpmErrors (clarity:rmuir:94017814__CE75E6D5-6EFB-4F99-93B2-2C2E949FACBC:none)

Begin Error Information:
Step Action Id: 5310368
Process Instance Id: 8718030
Step Instance Id: 14652079
Error: bpm.raw.message
Error Code: bpm.raw.message
Exception Trace: XOG URL: http://cnmlw-esxvm-321:8080/niku/xog
End Error Information


ERROR 2020-01-16 15:07:39,776 [Custom script execution pool-11-thread-12] utilities.BpmErrors (clarity:rmuir:94017814__CE75E6D5-6EFB-4F99-93B2-2C2E949FACBC:none)

Begin Error Information:
Step Action Id: 5310368
Process Instance Id: 8718030
Step Instance Id: 14652079
Error: bpm.raw.message
Error Code: bpm.raw.message
Exception Trace: [XOG]Logging into Clarity XOG at http://cnmlw-esxvm-321:8080.
End Error Information


ERROR 2020-01-16 15:07:39,842 [Custom script execution pool-11-thread-12] utilities.BpmErrors (clarity:rmuir:94017814__CE75E6D5-6EFB-4F99-93B2-2C2E949FACBC:none)

Begin Error Information:
Step Action Id: 5310368
Process Instance Id: 8718030
Step Instance Id: 14652079
Error: bpm.raw.message
Error Code: bpm.raw.message
Exception Trace: [XOG][XOG]Log in successful 94018831__2CC4DC4B-A8EA-4375-8719-B798A7E98ACE
End Error Information


ERROR 2020-01-16 15:07:39,843 [Custom script execution pool-11-thread-12] utilities.BpmErrors (clarity:rmuir:94017814__CE75E6D5-6EFB-4F99-93B2-2C2E949FACBC:none)

Begin Error Information:
Step Action Id: 5310368
Process Instance Id: 8718030
Step Instance Id: 14652079
Error: bpm.raw.message
Error Code: bpm.raw.message
Exception Trace: XML for build a product =<?xml version="1.0" encoding="UTF-8"?>
<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>
<CustomInformation>
<ColumnValue name="o2_area">
<Value>o2_s19</Value>
<Value>o2_s20</Value>
<Value>o2_s21</Value>
<Value>o2_s22</Value>
<Value>o2_s23</Value>
<Value>o2_s24</Value>
<Value>o2_s25</Value>
<Value>o2_s26</Value>
<Value>o2_s27</Value>
</ColumnValue>
</CustomInformation>
</Idea>
</Ideas>
</NikuDataBus>

End Error Information


ERROR 2020-01-16 15:07:39,904 [Custom script execution pool-11-thread-12] utilities.BpmErrors (clarity:rmuir:94017814__CE75E6D5-6EFB-4F99-93B2-2C2E949FACBC:none)

Begin Error Information:
Step Action Id: 5310368
Process Instance Id: 8718030
Step Instance Id: 14652079
Error: bpm.raw.message
Error Code: bpm.raw.message
Exception Trace: [XOG]XOG operation completed [Total 0, Inserted 0, Updated 0, Failed 0]
End Error Information


ERROR 2020-01-16 15:07:39,904 [Custom script execution pool-11-thread-12] utilities.BpmErrors (clarity:rmuir:94017814__CE75E6D5-6EFB-4F99-93B2-2C2E949FACBC:none)

Begin Error Information:
Step Action Id: 5310368
Process Instance Id: 8718030
Step Instance Id: 14652079
Error: bpm.raw.message
Error Code: bpm.raw.message
Exception Trace: [XOG]Error Information:
End Error Information


ERROR 2020-01-16 15:07:39,922 [Custom script execution pool-11-thread-12] utilities.BpmErrors (clarity:rmuir:94017814__CE75E6D5-6EFB-4F99-93B2-2C2E949FACBC:none)

Begin Error Information:
Step Action Id: 5310368
Process Instance Id: 8718030
Step Instance Id: 14652079
Error: bpm.raw.message
Error Code: bpm.raw.message
Exception Trace: [XOG]Logged out
End Error Information


ERROR 2020-01-16 15:07:39,923 [Custom script execution pool-11-thread-12] utilities.BpmErrors (clarity:rmuir:94017814__CE75E6D5-6EFB-4F99-93B2-2C2E949FACBC:none)

Begin Error Information:
Step Action Id: 5310368
Process Instance Id: 8718030
Step Instance Id: 14652079
Error: bpm.raw.message
Error Code: bpm.raw.message
Exception Trace: Time taken: 0.16 seconds
End Error Information


ERROR 2020-01-16 15:07:39,930 [Custom script execution pool-11-thread-12] utilities.BpmErrors (clarity:rmuir:94017814__CE75E6D5-6EFB-4F99-93B2-2C2E949FACBC:none)

Begin Error Information:
Step Action Id: 5310368
Process Instance Id: 8718030
Step Instance Id: 14652079
Error: bpm.customScriptDone
Error Code: bpm.customScriptDone
End Error Information

[David Morton]

That is curious ; if I were trying to debug this I would put some gel:log/out statements in at the point that you write the idea_code and idea_name to the v_idea XML
(so that would tell me if I was getting the data I expected correctly out of the first SQL) - and I'd probably also dump the entire v_idea XML out immediately after I had built it (i.e. before the second bit of SQL). And dump it out before/after you build the indata XML (dump that out too as soon as you build it); my suspicion would be that its losing something either in that first SQL statement or when you try to build the second XML from the first (I struggle to debug that XPATH syntax by sight)!

Good luck.

[ROBBIE MUIR]

Thanks David.  Will see what I can come up with following your advise