AnsweredAssumed Answered

Xog-Gel Query requesting to help in my training

Question asked by gamya on Sep 16, 2013
Latest reply on Sep 16, 2013 by Dave
Hi,
I want to read task data from project and update it automatically into subobject called Task Information which is subobject to status report who's master object
is project.In my projects page i have 4 projects which are defined by me.I am sending a Gel please go through it are there any mistakes.Because when i am using
the following Xog-Gel i am getting information information in only one project but not in the project where i exactly need while running the process can you plese help
me how to populate the data automatically from project to its grandoject
<gel:script 
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:core="jelly:core" 
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" 
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" 
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:xog="http://www.niku.com/xog" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


<!-- Description: GEL script that will create Task Details object instances using XOG -->

<!-- Author: Raghu Bongu -->

<!-- Version: 1.0 -->

<!-- Created Dt: 9/6/13 --> 

<!-- The XOG URL is taken from the properties file-->

<gel:parse file="../config/properties.xml" var="properties"/>

<util:tokenize delim="/" var="entryUrl">


<gel:expr select="$properties//webServerInstance[@id='app']/@entryUrl"/>

</util:tokenize>

<core:set value="http://${entryUrl[1]}" var="XOGURL"/>

<!-- end get URL -->



<gel:log>XOG URL = ${XOGURL}</gel:log>





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




<gel:log>Object Instance ID - ${gel_objectInstanceId}</gel:log>





<!-- get username and sessionId -->




<gel:parameter var="username"/>




<core:new className="com.niku.union.security.DefaultSecurityIdentifier" var="secId" />




<core:invokeStatic var="userSessionCtrl" className="com.niku.union.security.UserSessionControllerFactory" method="getInstance" />




<core:set var="secId" value="${userSessionCtrl.init(username, secId)}"/>




<core:set var="XOGUsername" value="${secId.getUserName()}"/>




<core:set var="sessionID" value="${secId.getSessionId()}"/>




<!-- a username will need to be provided on the Script Parameters page.  not selected from single location at this time -->




<gel:log>Username for XOG set - ${XOGUsername}</gel:log>




<gel:log>Session ID set - ${sessionID}</gel:log>




<!-- end get username and session -->

<!-- Check XOG sessionID -->

<core:choose>

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

<gel:log category="XOG" level="INFO">Login Failed.  No Session ID.</gel:log>

</core:when>

<core:otherwise>

<gel:log category="XOG" level="INFO">Login successful.  Session ID - ${sessionID}</gel:log>

<!-- Begin XOG Elements -->

<!-- XOG Header -->

<gel:parse var="gstskinfrmInts">

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

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

<customObjectInstances objectCode="gs_tsk_infrm">

<!--Custom object instances go here -->
    </customObjectInstances>


</NikuDataBus>


</gel:parse>



<!-- Get the Task Details on this project object instance -->



<sql:query dataSource="${clarityDS}" var="result" escapeText="false">





select 
t.prname,t.prstatus,tm.t_manager, 






to_char(t.prstart, 'YYYY-MM-DD'),






to_char(t.PRFINISH, 'YYYY-MM-DD'),






t.prexternalid,






(select code from inv_investments where id = t.prprojectid) gpar_code,






(select code from ODF_CA_GS_STATUS_REPORT WHERE ID = ${gel_objectInstanceId}) par_code,






PRJ_EFFORT_FCT(t.prid,'task','ETC') / 3600




from 
prtask t,odf_ca_task tm




where  t.prprojectid=(SELECT ODF_PARENT_id FROM ODF_CA_GS_STATUS_REPORT WHERE ID = ${gel_objectInstanceId} )




      and t.prid=tm.iD





  
            </sql:query>









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




<core:set value="${rrow[i+0]}" var="task_name"/>




<core:set value="${rrow[i+1]}" var="task_status"/>




<core:set value="${rrow[i+2]}" var="task_manager"/>




<core:set value="${rrow[i+3]}" var="start"/>




<core:set value="${rrow[i+4]}" var="finish"/>




<core:set value="${rrow[i+5]}" var="uniq_code"/>




<core:set value="${rrow[i+6]}" var="gpar_code"/>




<core:set value="${rrow[i+7]}" var="par_code"/>




<core:set value="${rrow[i+8]}" var="task_etc"/>






<gel:parse var="InstRows">



<instance grandparentInstanceCode="${gpar_code}" grandparentObjectCode="project" instanceCode="${uniq_code}" objectCode="gs_tsk_infrm"




  parentInstanceCode="${par_code}" parentObjectCode="gs_status_report">




  <CustomInformation>





<ColumnValue name="gs_task_mgr">${task_manager}</ColumnValue>





<ColumnValue name="gs_task_status">${task_status}</ColumnValue>





<ColumnValue name="task_finish">${finish}T17:00:00</ColumnValue>





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





<ColumnValue name="name">${uniq_code}</ColumnValue>





<ColumnValue name="code">${uniq_code}</ColumnValue>





<ColumnValue name="odf_parent_id">${gel_objectInstanceId}</ColumnValue>





<ColumnValue name="odf_cncrt_parent_id">${gel_objectInstanceId}</ColumnValue>





<ColumnValue name="task_start">${start}T08:00:00</ColumnValue>





<ColumnValue name="gs_task_etc">${task_etc}</ColumnValue>




  </CustomInformation>




  </instance>



</gel:parse>                  
<gel:set insert="true" select="$gstskinfrmInts/NikuDataBus/customObjectInstances" value="${InstRows}"/>
</core:forEach>
<gel:log level="DEBUG"><gel:expr select="$gstskinfrmInts"/></gel:log>
<!-- Make a soap:invoke to create these instances -->
<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="$gstskinfrmInts"/>

</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
<!-- End of soap:invoke -->

<!-- Analyse the XOG Output -->
<gel:set asString="true" select="$runresult/soapenv:Envelope/soapenv:Body/XOGOutput/Status/@state" var="XOGoutcome"/>
<gel:log level="info" message="Status: ${XOGoutcome}"/>
<core:switch on="${XOGoutcome}">
<core:case value="SUCCESS">
<gel:set asString="false" select="$runresult/soapenv:Envelope/soapenv:Body/XOGOutput/Statistics" var="stats"/>
<gel:out>Success! Total number of records: <gel:expr select="$stats/@totalNumberOfRecords"/></gel:out>
</core:case>
<core:case value="FAILURE">
<gel:set asString="false" select="$runresult/soapenv:Envelope/soapenv:Body/XOGOutput/Statistics" var="stats"/>
<gel:out> XOG failed. Out of <gel:expr select="$stats/@totalNumberOfRecords"/> 
  records, <gel:expr select="$stats/@failureRecords"/> failed.
</gel:out>
</core:case>
<core:default>
<gel:out>Couldn't find XOG output summary. Please check the output file manually.</gel:out>
</core:default>
</core:switch>

<!-- finally logout and close the XOG session -->
<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>
<!-- END OF If session is valid -->

</core:otherwise>
</core:choose>
</gel:script>

Outcomes