Looking for a few pointers with GEL and properties.xml

Discussion created by adam.a.flanagan on Nov 15, 2010
Latest reply on Nov 15, 2010 by adam.a.flanagan

I'm just starting out with GEL and I've written a fairly simple script to update cost details. It validates through the command line but won't run, which I think is due to not being able to grab the properties.xml to know where the db is. Ho do I pass it the file ? Or place the file correctly for it to get picked up ?? Also, when I run this inside a process I get an error about badly formed XML.

Any help gratefully appreciated.
<!-- Some header stuff -->
<gel:script xmlns:core="jelly:core"
    xmlns:j="jelly:core" xmlns:jx=""
    xmlns:sql="jelly:sql" xmlns:util="jelly:util"
    xmlns:xog="http://localhost/niku/xog" xmlns:xsi="">

    <!-- XOG web server & credentials -->
    <gel:parameter default="admin" var="XOGusername"/>
    <gel:parameter default="password" secure="false" var="XOGpassword"/>
    <gel:parameter default="" var="XOGserver"/>

<!-- Get a DB Connection to Clarity -->
    <gel:setDataSource dbId="Niku"/>

<!-- Setup a var for debugging purposes -->

<core:set value="${gel_objectInstanceId}" var="intSpend"/>

<!--<core:set value="5013438" var="intSpend"/>-->

<!-- Get the details of the project -->

<j:catch var="errGetProject">
        <sql:query escapeText="false" var="GetProject">                            


FROM inv_investments I, fin_plans CP, fin_plans BP, biz_com_periods SP, biz_com_periods EP

WHERE CP.object_id = 

AND = 'Cost Plan' 

AND BP.object_id = 

AND = 'Hard Benefits' 

AND = CP.start_period_id 

AND = CP.end_period_id 

AND IN ( SELECT odf_parent_id FROM odf_ca_gsk_spend WHERE id = ? )

  <sql:param value="${intSpend}"/>
        <j:when test="${errGetProject != null}">
            <gel:log level="ERROR">SQL Error on getting project details ${errGetProject}</gel:log>
            <core:set value="${GetProject.rows[0].prj_id}" var="intProjectID"/>
            <core:set value="${GetProject.rows[0].prj_name}" var="strProjectName"/>
            <core:set value="${GetProject.rows[0].prj_code}" var="strProjectCode"/>
            <core:set value="${GetProject.rows[0].cp_code}" var="strCostCode"/>
            <core:set value="${GetProject.rows[0].bp_code}" var="strBenCode"/>
            <core:set value="${GetProject.rows[0].start_period}" var="strStartPeriod"/>
            <core:set value="${GetProject.rows[0].end_period}" var="strEndPeriod"/>

<!-- Get the spends/transactions -->

<j:catch var="errSpends">
        <sql:query escapeText="false" var="GetSpends">                            

SELECT exp_id, cat_id, TO_CHAR(start_spend, 'YYYY-MM-DD') start_spend, TO_CHAR(end_spend, 'YYYY-MM-DD') end_spend, 

 TO_CHAR(SUM(spend_amt), 999999999.9) spend_amt, exp_id || '_' || cat_id Detail


SELECT SP.expenditure, EX.prexternalid EXP_ID, EX.prname EXP_NAME, 

SP.category, TC.transclass CAT_ID, TC.description CAT_NAME,, SP.spend_amt, 


TO_DATE('01/'||TO_CHAR(SP.spend_date, 'MM/YYYY'), 'DD/MM/YYYY') START_SPEND,  

ADD_MONTHS(TO_DATE('01/'||TO_CHAR(SP.spend_date, 'MM/YYYY'), 'DD/MM/YYYY'), 1) - 1 END_SPEND 

FROM odf_ca_gsk_spend SP, prchargecode EX, transclass TC 

WHERE SP.expenditure = EX.prid 

AND SP.category = 

AND SP.odf_parent_id = ? 


GROUP BY exp_id, cat_id, start_spend, end_spend 

ORDER BY exp_id, cat_id, start_spend ASC 

<sql:param value="${intProjectID}"/>
        <j:when test="${errSpends != null}">
            <gel:log level="ERROR">SQL Error on getting spends/transactions ${errSpends}</gel:log>

<!-- Do our ting -->

<!-- Build the main XML -->

<gel:parse var="xmlDocument">

<NikuDataBus xmlns:xsi="" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_costPlan.xsd">

<Header action="write" externalSource="NIKU" objectType="costPlan" version=""/>


<CostPlan benefitPlanCode="${strBenCode}" code="${strCostCode}"

finishPeriod="${strEndPeriod}" investmentCode="${strProjectCode}"

investmentType="PROJECT" isPlanOfRecord="1" name="Cost Plan"

periodType="MONTHLY" primaryPlanBy="CHARGECODE"

secondaryPlanBy="TRANSCLASS" startPeriod="${strStartPeriod}">


<CustomInformation><ColumnValue name="partition_code">NIKU.ROOT</ColumnValue></CustomInformation>





<!-- Build a node template -->

<gel:parse var="nodeTemp">

<Detail detail2Name="category" detailName="expenditure">


<ColumnValue name="gsk_it_cr_currency">GBP</ColumnValue>

<ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>



<PlanData cost="0.0" end_date="2010-12-31"
revenue="0.0" start_date="2010-12-01" units="0.0"/>


<!-- Setup a switch or two -->

<core:set value="" var="strCurrentDetail"/>

<!-- Iterate through the rows -->

<core:forEach items="${GetSpends.getRows()}" var="row">

<core:if test="${row.Detail != strCurrentDetail}">

<!-- start a new detail up -->

<core:set value="${row.Detail}" var="strCurrentDetail"/>

<!-- grab a copy of the template -->

<gel:set select="$nodeTemp/Detail" var="detailNode"/>

<!-- update the values -->

<gel:set value="${row.exp_id}" select="$detailNode/@detailName"/>

<gel:set value="${row.cat_id}" select="$detailNode/@detail2Name"/>

<!-- insert it into the master doc -->

<gel:set value="${detailNode}" select="$xmlDocument/Details" insert="true"/>


<!-- spit the row -->

<!-- grab a copy of the template -->

<gel:set select="$nodeTemp/PlanData" var="planNode"/>

<!-- update the values -->

<gel:set value="${row.spend_amt}" select="$planNode/@cost"/>

<gel:set value="${row.start_spend}" select="$planNode/@start_date"/>

<gel:set value="${row.end_spend}" select="$planNode/@end_date"/>

<!-- insert it into the master doc -->

<gel:set value="${planNode}" select="$xmlDocument/Details/Detail" insert="true"/>


<!-- send output to a file 

<core:catch var="serializeThis">

<gel:serialize fileName="update_cost_plan_debug.xml" var="${xmlDocument}"/>



<!-- ok, now we need to zap this thing in -->

<core:catch var="thisXog">

<soap:invoke endpoint="${XOGserver}" var="result">


xmlns:soap-env="" xmlns:xog="">


<xog:Login xmlns="">




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







<!-- End do our ting -->