AnsweredAssumed Answered

XOG Error Capture

Question asked by Lowell on Nov 16, 2010
Latest reply on Jan 12, 2011 by Chris_Hackett
I'm looking to capture a "XOGStatus" message for my GEL script. Currently, the XOGStatus message returns ""

Complete Script below:

<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="jelly:sql" xmlns:xog="http://www.niku.com/xog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<!-- Import EPMLive Timesheet Values: Written by: Lowell Wetzel 10/28/2010-->

<!--Username/Password in Admin Document-->

<gel:parameter default="./outputfiles" var="PLocalDirectory"/>

<gel:parameter default="http://vmslcclarity02:80" var="XOGURL"/>
<gel:parameter default="admin" var="XOGUsername"/>
<gel:parameter default="admin" secure="true" var="XOGPassword"/>

<!-- Initialize some counters. -->
<core:set value="0" var="totalCount"/>
<core:set value="0" var="insertCount"/>
<core:set value="0" var="updateCount"/>

<!-- Log into XOG first. -->
<soap:invoke endpoint="${XOGURL}/niku/xog" var="auth">
<soap:message>
<soap-env:Envelope
xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
<soap-env:Header/>
<soap-env:Body>
<xog:Login>
<xog:Username>${XOGUsername}</xog:Username>
<xog:Password>${XOGPassword}</xog:Password>
</xog:Login>
</soap-env:Body>
</soap-env:Envelope>
</soap:message>
</soap:invoke>
<!-- Checking whether a sessionID is returned. If not, it means that Login was not successful. -->
<gel:set asString="true"
select="$auth/SOAP-ENV:Envelope/SOAP-ENV:Body/xog:SessionID/text()" var="sessionID"/>
<core:choose>
<core:when test="${sessionID == null}">
<gel:log level="ERROR">Cannot login to Clarity XOG. Check username and password.</gel:log>
</core:when>
<!--Otherwise-->
<core:otherwise>
<gel:setDataSource dbId="niku"/>

<core:set var="vnow">
<gel:formatDate format="yyyy-MM-dd"/>
</core:set>

<!--Count Rows to process-->
<sql:query var="v_toprocess">
select count(*) as num
from trg_epmlive_stage t
where
t.timesheetid is not null
and t.taskassignmentid is not null
and t.prresourceid is not null
and t.prtimeperiodid is not null
and t.taskinternaltaskid is not null
and (T.prstatus = 0 or T.prstatus = 2)



</sql:query>

<gel:log level="INFO">Number of timesheets to process: ${v_toprocess.rows[0].num}</gel:log>
<!--Time Periods-->
<sql:query var="v_ts1">
SELECT distinct (T.prtimeperiodid)
FROM trg_epmlive_stage T
where
T.timesheetid is not null
and T.taskassignmentid is not null
and T.prresourceid is not null
and T.prtimeperiodid is not null
and T.taskinternaltaskid is not null
and (T.prstatus = 0 or T.prstatus = 2)


</sql:query>
<!--Parse-->
<gel:parse var="xogBody">

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_timeperiod.xsd">
<Header action="write" externalSource="NIKU" objectType="timeperiod" version="8.1.3.4545"/>
<TimePeriods>
<!--Step1, For Each Time Period-->
<core:forEach items="${v_ts1.rows}" trim="true" var="tp1">

<!--First start with the Time Period -->
<sql:query var="v_ts2">
SELECT to_char(prstart,'yyyy-mm-dd') || 'T00:00:00' prstart, to_char(prfinish,'yyyy-mm-dd') || 'T00:00:00' prfinish,
case prisopen
when 1 then 'true'
when 0 then 'false' end prisopen
FROM prtimeperiod
where prid = ${tp1.prtimeperiodid}




</sql:query>

<TimePeriod finish="${v_ts2.rows[0].prfinish}" openForTimeEntry="${v_ts2.rows[0].prisopen}" start="${v_ts2.rows[0].prstart}">
<TimeSheets>

<!--Step2, Select the individual resource by time period-->
<sql:query var="v_ts3">
SELECT distinct T.prresourceid from trg_epmlive_stage T
where T.prtimeperiodid = ${tp1.prtimeperiodid}
and T.taskassignmentid is not null
and T.taskinternaltaskid is not null
and T.prresourceid is not null
and T.prprojectid is not null
and (T.prstatus = 0 or T.prstatus = 2)
</sql:query>

<!--Step2, By Time Period, By Resource, Populate Time Sheet -->
<core:forEach items="${v_ts3.rows}" trim="true" var="tp2">

<!--Step3, Query to Populate the Timesheet Header based on the Time Period from above and by Resource-->
<sql:query var="v_ts4">
SELECT distinct T.resourceid, T.timesheetid, T.prresourceid, T.prstatus, T.prversion
from trg_epmlive_stage T
where T.prtimeperiodid = ${tp1.prtimeperiodid}
and T.prresourceid = ${tp2.prresourceid}
and T.taskassignmentid is not null
and T.timesheetid is not null
and T.taskinternaltaskid is not null
and T.prresourceid is not null
and T.prprojectid is not null
and (T.prstatus = 0 or T.prstatus = 2)
</sql:query>

<!--Step3, Loop through to populate the time sheet by time period, by resource -->
<core:forEach items="${v_ts4.rows}" trim="true" var="tp3">
<!--troubleshoot 2 loops-->
<gel:log level="INFO">timesheetID "${tp3.timesheetid}" ResourceID "${tp3.resourceid}" PRSTATUS "${tp3.prstatus}" PRVersion "${tp3.prversion}"</gel:log>


<TimeSheet ID="${tp3.timesheetid}"
resourceID="${tp3.resourceid}" status="1"
submittedBy="${tp3.prresourceid}" version="${tp3.prversion}">
<TimeSheetEntries>

<!--Step4, Query to Populate the Timesheets based on the Time Period from above and by Resource-->
<sql:query var="v_ts5">
SELECT T.resourceid, T.projectid, T.taskid, to_char(T.actualdate1,'yyyy-mm-dd') actualdate1, T.value1,
to_char(T.actualdate2,'yyyy-mm-dd') actualdate2, T.value2, to_char(T.actualdate3,'yyyy-mm-dd') actualdate3, T.value3,
to_char(T.actualdate4,'yyyy-mm-dd') actualdate4, T.value4, to_char(T.actualdate5,'yyyy-mm-dd') actualdate5, T.value5,
to_char(T.actualdate6,'yyyy-mm-dd') actualdate6, T.value6, to_char(T.actualdate7,'yyyy-mm-dd') actualdate7, T.value7,
T.taskassignmentid, T.taskchargecodeid, T.taskinternaltaskid, T.timesheetid, T.prtimeperiodid, T.prresourceid, T.taskprchargecodeid, T.prprojectid,
T.prstatus, T.prversion, T.role_id, T.typecodeid, T.typecodename
from trg_epmlive_stage T
where T.prtimeperiodid = ${tp1.prtimeperiodid}
and T.prresourceid = ${tp2.prresourceid}
and T.taskassignmentid is not null
and T.timesheetid is not null
and T.taskinternaltaskid is not null
and T.prresourceid is not null
and T.prprojectid is not null
and (T.prstatus = 0 or T.prstatus = 2)
</sql:query>

<!--Step4, Loop through to Populate the Timesheets based on the Time Period from above and by Resource-->
<core:forEach items="${v_ts5.rows}" trim="true" var="tp4">

<TimeSheetEntry
assignmentID="${tp4.taskassignmentid}"
chargeCodeID="${tp4.taskchargecodeid}"
internalTaskID="${tp4.taskinternaltaskid}"
projectID="${tp4.projectid}"
roleID="${tp4.role_id}"
taskID="${tp4.taskid}"
typeCodeID="${tp4.typecodeid}" typeCodeName="${tp4.typecodename}">
<DailyActuals>
<Actual actualDate="${tp4.actualdate1}" amount="${tp4.value1}"/>
<Actual actualDate="${tp4.actualdate2}" amount="${tp4.value2}"/>
<Actual actualDate="${tp4.actualdate3}" amount="${tp4.value3}"/>
<Actual actualDate="${tp4.actualdate4}" amount="${tp4.value4}"/>
<Actual actualDate="${tp4.actualdate5}" amount="${tp4.value5}"/>
<Actual actualDate="${tp4.actualdate6}" amount="${tp4.value6}"/>
<Actual actualDate="${tp4.actualdate7}" amount="${tp4.value7}"/>
</DailyActuals>
</TimeSheetEntry>
<!--Step4 END-->
</core:forEach>
</TimeSheetEntries>
<Notes>
<NoteData createdBy="${tp3.resourceid}"
noteText="EPMLive Data"/>
</Notes>
</TimeSheet>
<!--Step3 END-->
</core:forEach>
<!--Step2 END-->
</core:forEach>
</TimeSheets>

</TimePeriod>
<!--Step1 END-->
</core:forEach>
</TimePeriods>

</NikuDataBus>
</gel:parse>

<gel:serialize
fileName="${PLocalDirectory}/XOG_EPMLive_${vnow}.xml" var="${xogBody}"/>

<!--<<<<<<<<<<<<<< XOG VALUES>>>>>>>>>>>>>>>>>>>-->
<!-- Send a XOG request. -->
<gel:log level="INFO">Sending XOG Request.</gel:log>

<soap:invoke endpoint="${XOGURL}/niku/xog" var="result">
<soap:message>
<soap-env:Envelope
xmlns:soap-env="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="$xogBody"/>
</soap-env:Body>
</soap-env:Envelope>
</soap:message>
</soap:invoke>
<gel:log level="INFO">XOG status is "${xogStatus}". </gel:log>
<!-- Logout XOG -->
<soap:invoke endpoint="${XOGURL}/niku/xog" var="result">
<soap:message>
<soap-env:Envelope
xmlns:soap-env="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>
</core:otherwise>

</core:choose>

<gel:log level="INFO">Number of timesheet rows to process is -- ${v_toprocess.rows[0].num}</gel:log>
<gel:log level="INFO">EPMLive xog: Finished</gel:log>

</gel:script>

Outcomes