Here's an example on how to do out through query:
<gel:script xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:q="http://www.niku.com/xog/Query"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="jelly:sql" xmlns:util="jelly:util" xmlns:x="jelly:xml"
xmlns:xog="http://www.niku.com/xog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<gel:setDataSource dbId="niku"/>
<!-- get info -->
<core:set value="Java version: ${java.version}" var="logMessage"/>
<gel:formatDate format="dd MMM yy HH:mm:ss.SSS" stringVar="logDate"/>
<core:set value="${logDate} - ${logMessage}" var="logMessage"/>
<gel:log level="INFO">${logMessage}</gel:log>
<core:invokeStatic className="java.util.ResourceBundle" method="getBundle" var="xog_version">
<core:arg type="java.lang.String" value="xog"/>
</core:invokeStatic>
<core:set value="XOG/GEL version: ${xog_version.getString('version')}" var="logMessage"/>
<gel:formatDate format="dd MMM yy HH:mm:ss.SSS" stringVar="logDate"/>
<core:set value="${logDate} - ${logMessage}" var="logMessage"/>
<gel:log level="INFO">${logMessage}</gel:log>
<!-- invoke -->
<core:new className="com.niku.xog.client.XOGClientInterface" var="xogcaller"/>
<core:new className="com.niku.xog.client.XOGAdminClient" var="xogadminclient"/>
<core:invokeStatic className="com.niku.union.config.ConfigurationManager" method="getInstance" var="thisClarityConfig"/>
<sql:query escapeText="false" var="todayQuery">
select convert(char, getdate(), 120) as theDate from dual
</sql:query>
<core:forEach items="${todayQuery.rows}" var="todayQueryRow">
<core:set var="today">${todayQueryRow.theDate}</core:set>
</core:forEach>
<!-- get session -->
<core:new className="com.niku.union.security.DefaultSecurityIdentifier" var="secId"/>
<core:invokeStatic className="com.niku.union.security.UserSessionControllerFactory" method="getInstance" var="userSessionCtrl"/>
<core:set value="${userSessionCtrl.init('admin', secId)}" var="secId"/>
<core:set value="${secId.getSessionId()}" var="thisSessionId"/>
<core:set value="XOG Session ID: ${thisSessionId}" var="logMessage"/>
<gel:formatDate format="dd MMM yy HH:mm:ss.SSS" stringVar="logDate"/>
<core:set value="${logDate} - ${logMessage}" var="logMessage"/>
<gel:log level="INFO">${logMessage}</gel:log>
<!-- get xog url -->
<core:set value="${thisClarityConfig.getProperties().getWebServer().getSchedulerUrl()}" var="clarity_url"/>
<core:if test="${clarity_url == ''}">
<core:switch on="${thisClarityConfig.getProperties().getWebServer().getWebServerInstance(0).getId()}">
<core:case value="nsa">
<core:set value="${thisClarityConfig.getProperties().getWebServer().getWebServerInstance(1).getEntryUrl()}" var="clarity_url"/>
</core:case>
<core:default>
<core:set value="${thisClarityConfig.getProperties().getWebServer().getWebServerInstance(0).getEntryUrl()}" var="clarity_url"/>
</core:default>
</core:switch>
</core:if>
<util:tokenize delim=":" var="urlComponents">${clarity_url}</util:tokenize>
<core:set value="${urlComponents.get(0)}://" var="httpMethod"/>
<core:set value="${urlComponents.get(1)}" var="servername"/>
<util:tokenize delim="/" var="servername">${servername}</util:tokenize>
<core:set value="${servername.get(0)}" var="servername"/>
<core:set value="${urlComponents.get(2)}" var="portnumber"/>
<core:choose>
<core:when test="${portnumber == null}">
<core:switch on="${httpMethod}">
<core:case value="https://">
<core:set var="portnumber">443</core:set>
</core:case>
<core:default>
<core:set var="portnumber">80</core:set>
</core:default>
</core:switch>
</core:when>
<core:otherwise>
<util:tokenize delim="/" var="portnumber">${portnumber}</util:tokenize>
<core:set value="${portnumber.get(0)}" var="portnumber"/>
</core:otherwise>
</core:choose>
<core:set value="${httpMethod}${servername}:${portnumber}/niku/xog" var="clarity_url"/>
<core:set value="XOG URL: ${clarity_url}" var="logMessage"/>
<gel:formatDate format="dd MMM yy HH:mm:ss.SSS" stringVar="logDate"/>
<core:set value="${logDate} - ${logMessage}" var="logMessage"/>
<gel:log level="INFO">${logMessage}</gel:log>
<gel:log level="INFO">Uppdaterar investment cost plan</gel:log>
<!--=================== Get product price information ===================-->
<sql:query escapeText="false" var="v_qryresult">
<!--=================== query to get investment, product, selling instance, price data and investment cost plan ===================-->
select oczsp.id salj_int_id
,oczsp.code salj_code
,oczsp.name salj_name
,oczsp.odf_parent_id salj_parent
,oczsp.z_prod_artikel prod_int_id
,inv.id inv_int_id
,inv.code inv_code
,inv.name inv_name
,inv.odf_object_code investment_type
,prod.code prod_code
,prod.name prod_name
,oczkp.z_antal prod_antal
,oczkp.z_st_pris prod_st_pris
,real_inv.name real_inv_name
,bcp.period_name
,bcp.start_date period_start
,convert(char, bcp.start_date, 126) period_start_xog
,(bcp.end_date-1) period_end
,convert(char, (bcp.end_date-1), 126) period_end_xog
,s.SLICE
,s.slice_date
,(oczkp.z_st_pris * s.SLICE) manads_kostnad
,(case when
cost_plan.inv_id is null then inv.id
else cost_plan.inv_id
end) inv_id_cost
,(case when
cost_plan.inv_code is null then inv.code
else cost_plan.inv_code
end) inv_code_cost
,(case when
cost_plan.inv_name is null then inv.name
else cost_plan.inv_name
end) inv_name_cost
,(case when
cost_plan.plan_id is null then (select max(id) from FIN_PLANS) + (ROW_NUMBER() OVER (ORDER BY oczsp.id))
else cost_plan.plan_id
end) plan_id_cost
,(case when
cost_plan.plan_code is null then 'kost_plan'
else cost_plan.plan_code
end) plan_code_cost
,(case when
cost_plan.plan_name is null then 'kost_plan'
else cost_plan.plan_name
end) plan_name_cost
,(case when
(case when
cost_plan.benefit_plan_id is null then '1'
else cost_plan.benefit_plan_id
end) = '1' then ' '
end) benefit_plan_id_cost
--,(case when
-- cost_plan.start_period_id is null then
-- else cost_plan.start_period_id
-- end) start_period_id_cost
,(case when
cost_plan.start_period_name is null then 'FY2016 JAN'
else cost_plan.start_period_name
end) start_period_name_cost
--,(case when
-- cost_plan.end_period_id is null then
-- else cost_plan.end_period_id
-- end) end_period_id_cost
,(case when
cost_plan.end_period_name is null then 'FY2016 DEC'
else cost_plan.end_period_name
end) end_period_name_cost
,(case when
cost_plan.investment_type is null then inv.odf_object_code
else cost_plan.investment_type
end) investment_type_cost
,(case when
cost_plan.revision is null then '0'
else cost_plan.revision
end) revision_cost
,(case when
(case when
cost_plan.IS_PLAN_OF_RECORD is null then '1'
else cost_plan.IS_PLAN_OF_RECORD
end) = 1 then 'true'
end) IS_PLAN_OF_RECORD_cost
from ODF_CA_Z_SALJ_PROD oczsp
join inv_investments inv on inv.id = oczsp.z_cc_projekt_layout
join inv_investments prod on prod.id = oczsp.z_prod_artikel
join ODF_CA_Z_KOPPLAD_PRODUKT oczkp on oczkp.z_produkt = oczsp.z_prod_artikel
join inv_investments real_inv on real_inv.id = oczkp.odf_parent_id
join ODF_SL_5036013 S ON oczsp.id = S.PRJ_OBJECT_ID
JOIN PRJ_BLB_SLICEREQUESTS SR ON S.SLICE_REQUEST_ID = SR.ID
JOIN ENTITY E ON INV.ENTITY_CODE = E.ENTITY
JOIN BIZ_COM_PERIODS bcp ON E.ID = bcp.ENTITY_ID and bcp.start_date = s.SLICE_DATE AND bcp.PERIOD_TYPE = 'MONTHLY'
left join
(
select distinct inv.id inv_id
,inv.code inv_code
,inv.name inv_name
,fp.id plan_id
,fp.code plan_code
,fp.name plan_name
,fp.benefit_plan_id
,fp.start_period_id
,bcp_start.period_name start_period_name
,fp.end_period_id
,bcp_end.period_name end_period_name
,fp.object_code investment_type
,fp.revision
,fp.IS_PLAN_OF_RECORD
from inv_investments inv
join FIN_PLANS fp on fp.object_id = inv.id and fp.plan_type_code = 'FORECAST'
join BIZ_COM_PERIODS bcp_start on bcp_start.id = fp.start_period_id
join BIZ_COM_PERIODS bcp_end on bcp_end.id = fp.end_period_id
where inv.id = (select inv.id
from ODF_CA_Z_SALJ_PROD oczsp
join inv_investments inv on inv.id = oczsp.z_cc_projekt_layout
where oczsp.id = ${gel_objectInstanceId}
--where oczsp.id = '5016008'
)
and fp.IS_PLAN_OF_RECORD = '1'
) cost_plan on cost_plan.inv_id = inv.id
--where oczsp.id = '5016008'
where oczsp.id = ${gel_objectInstanceId}
</sql:query>
<!--=================== Store attributes ===================-->
<core:forEach trim="true" items="${v_qryresult.rows}" var="row">
<core:set var="Rownum" value="${row.Rownum}"/>
<core:set var="salj_int_id" value="${row.salj_int_id}"/>
<core:set var="salj_code" value="${row.salj_code}"/>
<core:set var="salj_name" value="${row.salj_name}"/>
<core:set var="salj_parent" value="${row.salj_parent}"/>
<core:set var="prod_int_id" value="${row.prod_int_id}"/>
<core:set var="inv_int_id" value="${row.inv_int_id}"/>
<core:set var="inv_code" value="${row.inv_code}"/>
<core:set var="inv_name" value="${row.inv_name}"/>
<core:set var="investment_type" value="${row.investment_type}"/>
<core:set var="prod_code" value="${row.prod_code}"/>
<core:set var="prod_name" value="${row.prod_name}"/>
<core:set var="real_inv_name" value="${row.real_inv_name}"/>
<core:set var="prod_antal" value="${row.prod_antal}"/>
<core:set var="prod_st_pris" value="${row.prod_st_pris}"/>
<core:set var="period_name" value="${row.period_name}"/>
<core:set var="period_start" value="${row.period_start}"/>
<core:set var="period_start_xog" value="${row.period_start_xog}"/>
<core:set var="period_end" value="${row.period_end}"/>
<core:set var="period_end_xog" value="${row.period_end_xog}"/>
<core:set var="SLICE" value="${row.SLICE}"/>
<core:set var="slice_date" value="${row.slice_date}"/>
<core:set var="manads_kostnad" value="${row.manads_kostnad}"/>
<core:set var="inv_id_cost" value="${row.inv_id_cost}"/>
<core:set var="inv_code_cost" value="${row.inv_code_cost}"/>
<core:set var="inv_name_cost" value="${row.inv_name_cost}"/>
<core:set var="plan_id_cost" value="${row.plan_id_cost}"/>
<core:set var="plan_code_cost" value="${row.plan_code_cost}"/>
<core:set var="plan_name_cost" value="${row.plan_name_cost}"/>
<core:set var="benefit_plan_id_cost" value="${row.benefit_plan_id_cost}"/>
<core:set var="start_period_id_cost" value="${row.start_period_id_cost}"/>
<core:set var="start_period_name_cost" value="${row.start_period_name_cost}"/>
<core:set var="end_period_id_cost" value="${row.end_period_id_cost}"/>
<core:set var="end_period_name_cost" value="${row.end_period_name_cost}"/>
<core:set var="investment_type_cost" value="${row.investment_type_cost}"/>
<core:set var="revision_cost" value="${row.revision_cost}"/>
<core:set var="IS_PLAN_OF_RECORD_cost" value="${row.IS_PLAN_OF_RECORD_cost}"/>
<gel:log>
${gel_objectInstanceId}
</gel:log>
<!-- <gel:log> -->
<!-- Object instance: ${gel_objectInstanceId} -->
<!-- </gel:log> -->
<!--=================== Parse XOG read to set product name ===================-->
<gel:parse var="update_product">
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_costPlan.xsd">
<Header action="write" externalSource="NIKU" objectType="costPlan" version="14.3.0.298"/>
<CostPlans>
<CostPlan code="${row.plan_code_cost}" finishPeriod="${row.end_period_name_cost}" investmentCode="${row.inv_code_cost}" investmentType="${row.investment_type_cost}" isPlanOfRecord="${row.IS_PLAN_OF_RECORD_cost}" name="${row.plan_name_cost}" periodType="MONTHLY" revision="${row.revision_cost}" startPeriod="${row.start_period_name_cost}">
<Description/>
<GroupingAttributes>
<GroupingAttribute>lov1_id</GroupingAttribute>
</GroupingAttributes>
<Details>
<Detail>
<Cost>
<segment finish="${row.period_end_xog}" start="${row.period_start_xog}" value="${row.manads_kostnad}"/>
</Cost>
<Units/>
<Revenue/>
<GroupingAttributes>
<GroupingAttribute code="lov1_id" value="z_sek"/>
</GroupingAttributes>
<CustomInformation>
<ColumnValue name="partition_code">z_standard</ColumnValue>
<ColumnValue name="text">${row.real_inv_name}</ColumnValue>
</CustomInformation>
</Detail>
</Details>
</CostPlan>
</CostPlans>
</NikuDataBus>
</gel:parse>
<!--=================== Run the parsed xog read file ===================-->
<core:choose>
<core:when test="${thisSessionId == null}">
<gel:log category="XOG" level="ERROR">Couldn't Log in to XOG. Please verify the username/password.</gel:log>
</core:when>
<core:otherwise>
<core:catch var="v_xog_exception">
<soap:invoke endpoint="${clarity_url}" var="vResults">
<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>${thisSessionId}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<gel:include select="$update_product"/>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
</core:catch>
<core:choose>
<core:when test="${v_xog_exception != null}">
<gel:log category="XOG Failed" level="ERROR">XOG failed: ${v_xog_exception}</gel:log>
</core:when>
<core:otherwise>
<gel:log><gel:expr select="$vResults"/></gel:log>
</core:otherwise>
</core:choose>
</core:otherwise>
</core:choose>
<!--=================== Parse XOG read to update kopplad_product information ===================-->
<core:if test="${update_product != null}">
<gel:log level="WARNING">An error occurred :</gel:log>
<gel:log level="WARNING">${update_product}</gel:log>
</core:if>
</core:forEach>
</gel:script>