Owen_R

GEL Script outputting numerics as strings

Discussion created by Owen_R on Nov 26, 2010
Latest reply on Nov 26, 2010 by Owen_R
I have a script which creates new instances of a project sub-object when certain criteria are met.

I'm getting the data into the XOG write variable OK, but for some reason, the numeric/data values from the query are being put into the XML as strings, so the write call fails.

Here's the script
<gel:script
xmlns:x="jelly:xml"
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:soap-env="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">

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

<gel:parameter default="http://d002scm01" var="XOGURL"/>

<gel:parameter var="XOGusername" default="default"/>

<gel:parameter var="XOGpassword" default="default"/>



<gel:out>


Creating new NRR entries for next period

</gel:out>


<!-- Get data -->

<!-- Records to be processed - Previous step in the Clarity process will have set them to status 3 (pending) -->

<sql:query var="queryResult" dataSource="${clarityDS}">


select


count(nrr.id) as to_process


from


odf_ca_axa_nrr nrr,


ppa_transcontrol tc


where


nrr.code = tc.external_id


and nrr.status = 3


and nrr.copy_next_period = 1

</sql:query>


<core:forEach trim="true" items="${queryResult.rowsByIndex}" var="row">


<core:forEach var="field" items="${queryResult.columnNames}" indexVar="i" step="1">



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


</core:forEach>


<!-- Do something with the variables -->


<gel:log level="INFO" category="SQL">NRR Entries to process: ${to_process}</gel:log>


</core:forEach>


<!-- Get the data for the records to be copied. -->
<sql:query var="nrr" dataSource="${clarityDS}">

select

srm.unique_name as project_code,

nrr.odf_cncrt_parent_id,

nrr.odf_parent_id,

niku.COP_FORMATDATETIME_FCT(getdate(),'yyyy-mm-dd') as actuals_date,

nrr.task_id as task_id,

nrr.resource_id2


from

odf_ca_axa_nrr nrr,

ppa_transcontrol tc,

srm_projects srm


where

nrr.code = tc.external_id

and nrr.odf_parent_id = srm.id

and nrr.status = 3

and nrr.copy_next_period = 1

</sql:query>



<!-- ================================================= -->
<!-- Build some XML .-->
<!-- Note that XOG fails if you write an attribute which has a number lookup (bug CLRT-20830)
so resource_id2/task_id2 are used (and populated by database trigger on update of the nrr record)-->
<!-- ================================================= -->



<gel:parse var="new_nrr">


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



<Header action="write" externalSource="NIKU"




objectType="customObjectInstance"




overrideAutoNumbering ="false" version="12.0.6.5471"/>



<customObjectInstances objectCode="axa_nrr">




<core:forEach items="${nrr.rowsByIndex}" trim="true" var="trans">






<instance instanceCode="-1" objectCode="axa_nrr"





parentInstanceCode="${trans[0]}" parentObjectCode="project">




<CustomInformation>




<ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
<ColumnValue name="odf_cncrt_parent_id">"${trans[1]}"</ColumnValue>
<ColumnValue name="odf_parent_id">"${trans[2]}"</ColumnValue>
<ColumnValue name="code">""</ColumnValue>
<ColumnValue name="actuals_date">"${trans[3]}"</ColumnValue>




<ColumnValue name="actual_quantity">0</ColumnValue>
<ColumnValue name="task_id2">"${trans[4]}"</ColumnValue>




<ColumnValue name="resource_id2">"${trans[5]}"</ColumnValue>
<ColumnValue name="status">1</ColumnValue>




<ColumnValue name="notes">Automatically created</ColumnValue>





</CustomInformation>




</instance>



</core:forEach>



</customObjectInstances>



<XOGOutput xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/status.xsd">




<Object type="customObjectInstance"/>




<Status state="SUCCESS"/>




<Statistics failureRecords="0" insertedRecords="1"





totalNumberOfRecords="1" updatedRecords="0"/>




<Records/>



</XOGOutput>


</NikuDataBus>

</gel:parse>


<gel:log>


<gel:expr select="$new_nrr"/>

</gel:log>



<!-- XOG Login -->


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


<soap:message>



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




<soap-env:Body>





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






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






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





</xog:Login>




</soap-env:Body>



</soap-env:Envelope>


</soap:message>

</soap:invoke>


<gel:set var="sessionID" asString="true" select="$xog_login/soap-env:Envelope/soap-env:Body/xog:SessionID/text()"/>

<core:choose>


<core:when test="${sessionID == null}">



<gel:out>Couldn't Log in. Check the username/password.</gel:out>


</core:when>


<core:otherwise>







<!-- Run the built XML -->




- <core:catch var="v_xog_exception">




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




<soap:message>





<soap-env:Envelope xmlns:soapenv="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="$new_nrr"/>






</soap-env:Body>





</soap-env:Envelope>




</soap:message>



</soap:invoke>



</core:catch>




<gel:log>




<gel:expr select="$XOG_result"/>



</gel:log>


</core:otherwise>

</core:choose>




<!-- Log out of XOG -->


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


<soap:message>



<soap-env:Envelope xmlns:soapenv="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>





-->


</gel:script>



This is the XML that is created
<?xml version="1.0" encoding="UTF-8"?>
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">
    <Header action="write" externalSource="NIKU"
        objectType="customObjectInstance" overrideAutoNumbering="false" version="12.0.6.5471"/>
    <customObjectInstances objectCode="axa_nrr">
        <instance instanceCode="-1" objectCode="axa_nrr"
            parentInstanceCode="82056" parentObjectCode="project">
            <CustomInformation>
                <ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
                <ColumnValue name="odf_cncrt_parent_id">"5022937"</ColumnValue>
                <ColumnValue name="odf_parent_id">"5022937"</ColumnValue>
                <ColumnValue name="code">""</ColumnValue>
                <ColumnValue name="actuals_date">"2010-11-26"</ColumnValue>
                <ColumnValue name="actual_quantity">0</ColumnValue>
                <ColumnValue name="task_id2">"5172987"</ColumnValue>
                <ColumnValue name="resource_id2">"5021893"</ColumnValue>
                <ColumnValue name="status">1</ColumnValue>
                <ColumnValue name="notes">Automatically created</ColumnValue>
            </CustomInformation>
        </instance>
    </customObjectInstances>
    <XOGOutput xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/status.xsd">
        <Object type="customObjectInstance"/>
        <Status state="SUCCESS"/>
        <Statistics failureRecords="0" insertedRecords="1"
            totalNumberOfRecords="1" updatedRecords="0"/>
        <Records/>
    </XOGOutput>
</NikuDataBus>
ODF_CNCRT_PARENT_ID, ODF_PARENT_ID, TASK_ID2, RESOURCE_ID2 are all coming out in quotes when they should be true numerics. Same for ACTUALS_DATE which should be a date.

Am I doing something in wrong with my query results variable?

TIA

Owen

Outcomes