AnsweredAssumed Answered

Avail Start End Dates

Question asked by sanjeeb.chatterjee on Oct 19, 2018
Latest reply on Oct 19, 2018 by sanjeeb.chatterjee

#Convert Idea to Project

Hi Team,

We have a scenario where while converting the Idea to a project we are fetching the resources and updating the start date and End date of the resources (Attribute Avail Start and Avail End) with the project start date and end date. I could not find through the custom gel script how it is behaving and the business has requested to keep the dates as blank. Below is the script which is doing the same :- 

<gel:script xmlns:core="jelly:core" xmlns:email="jelly:email" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:ftp="jelly:com.niku.union.gel.FTPTagLibrary" 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:x="jelly:xml" 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"/>
<!-- GET SESSIONID BY USERNAME -->
<core:set value="admin" var="username"/>
<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(username, secId)}" var="secId"/>
<core:set value="${secId.getSessionId()}" var="sessionID"/>
<core:choose>
<core:when test="${sessionID == null}">
<gel:log level="ERROR">Cannot login to Clarity XOG. Check username.</gel:log>
</core:when>
<core:otherwise>
<gel:log>SessionID: ${sessionID}</gel:log>
</core:otherwise>
</core:choose>
<!-- QUERY to see if idea has team -->
<sql:query escapeText="0"
var="result"><![CDATA[
SELECT idea.code idea_code
, idea.name idea_name
,invi.code proj_code
,inv_ideas.INITIATOR_ID
FROM inv_investments invi
join inv_investments idea on idea.id = invi.idea_id
join inv_ideas inv_ideas on inv_ideas.id = idea.id
WHERE INVI.ID = ?
]]><sql:param value="${gel_objectInstanceId}"/>
</sql:query>
<core:set value="${result.rows[0].idea_code}" var="idea_code"/>
<core:set value="${result.rows[0].idea_name}" var="idea_name"/>
<core:set value="${result.rows[0].proj_code}" var="proj_code"/>
<core:set value="${result.rows[0].INITIATOR_ID}" var="INITIATOR_ID"/>
<core:choose>
<core:when test="${result.rowCount > 0}">
<gel:log>Result greater than 1!</gel:log>
<gel:setDataSource dbId="niku"/>
<!--Update investments ITS Contact to originating requestor -->
<!-- ESTABLISH VARIABLE TO COUNT ERRORS -->
<core:set value="0" var="errorCount"/>
<!-- BEGIN TRANSACTION TO INSERT INTO TABLE_NAME -->
<sql:transaction>
<!-- INSERT INTO TABLE_NAME -->
<core:catch var="error">
<sql:update escapeText="0"
var="totalInserts"><![CDATA[/*sql*/
UPDATE ODF_CA_INV
SET kc_its_contact = ?
WHERE ID = ?
]]><sql:param value="${INITIATOR_ID}"/>
<sql:param value="${gel_objectInstanceId}"/>
</sql:update>
</core:catch>
<core:if test="${error != null}">
<core:set value="1" var="errorCount"/>
</core:if>
<!-- COMMIT OR ROLLBACK SQL TRANSACTION -->
<core:choose>
<core:when test="${errorCount == 0}">
<!-- COMMIT TRANSACTION -->
<sql:update>COMMIT</sql:update>
<gel:log level="INFO">Inserted ${totalInserts} row(s)</gel:log>
</core:when>
<core:otherwise>
<!-- ROLLBACK TRANSACTION -->
<sql:update>ROLLBACK</sql:update>
<gel:log level="ERROR">SQL Error</gel:log>
<gel:log level="WARN">SQL Output: ${error}</gel:log>
</core:otherwise>
</core:choose>
</sql:transaction>
<!-- Read idea -->
<gel:parse var="loadContent">
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
<Header action="read" externalSource="NIKU" objectType="idea" version="8.0">
<args name="documentLocation" value="D:\niku/xog/test/documentXogLocation"/>
<args name="include_resources" value="true"/>
<args name="include_tasks" value="false"/>
<args name="include_allocations" value="true"/>
</Header>
<Query>
<Filter criteria="EQUALS" name="objectID">${idea_code}</Filter>
</Query>
</NikuDataBus>
</gel:parse>
<soap:invoke endpoint="Internal" var="idea_op">
<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="$loadContent"/>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
<gel:set asString="false" select="$idea_op/soapenv:Envelope/soapenv:Body/NikuDataBus/XOGOutput/Statistics" var="stats"/>
<gel:set asString="false" select="$stats/@failureRecords" var="failureRecs"/>
<core:if test="${failureRecs > 0}">
<gel:log level="ERROR">A Problem with the XOG happened.</gel:log>
<gel:log level="ERROR">Caught Exception was: <gel:expr select="$result"/>
</gel:log>
</core:if>
<!-- Set Idea Team node to variable -->
<gel:set select="$idea_op/soapenv:Envelope/soapenv:Body/NikuDataBus/Ideas/Idea/InvestmentResources" var="ideas_team"/>
<gel:parse var="prj_res">
<Resources/>
</gel:parse>
<!--For each resource add to prj_res node -->
<gel:forEach select="$ideas_team//Resource" var="idea_res">
<gel:set insert="true" select="$prj_res//Resources" value="${idea_res}"/>
</gel:forEach>
<!-- Read project -->
<gel:parse var="proj_read_xog">
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
<Header action="read" externalSource="NIKU" objectType="project" version="6.0.11">
<!-- you change the order by simply swap 1 and 2 number in the name attribute -->
<args name="order_by_1" value="name"/>
<args name="order_by_2" value="projectID"/>
<args name="include_tasks" value="false"/>
<args name="include_dependencies" value="false"/>
<args name="include_subprojects" value="false"/>
<args name="include_resources" value="true"/>
<args name="include_baselines" value="false"/>
<args name="include_allocations" value="false"/>
<args name="include_estimates" value="false"/>
<args name="include_actuals" value="false"/>
<args name="include_custom" value="true"/>
</Header>
<Query>
<Filter criteria="EQUALS" name="projectID">${proj_code}</Filter>
</Query>
</NikuDataBus>
</gel:parse>
<soap:invoke endpoint="Internal" var="result">
<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="$proj_read_xog"/>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
<gel:set asString="false" select="$result/soapenv:Envelope/soapenv:Body/NikuDataBus/XOGOutput/Statistics" var="stats"/>
<gel:set asString="false" select="$stats/@failureRecords" var="failureRecs"/>
<core:if test="${failureRecs > 0}">
<gel:log level="ERROR">A Problem with the XOG happened.</gel:log>
<gel:log level="ERROR">Caught Exception was: <gel:expr select="$result"/>
</gel:log>
</core:if>
<!-- Insert new resources -->
<gel:set select="$result/soapenv:Envelope/soapenv:Body/NikuDataBus/Projects/Project" var="project_node"/>
<gel:set insert="false" select="$project_node//Resources" value="${prj_res}"/>
<soap:invoke endpoint="Internal" var="result">
<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>
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_project.xsd">
<Header action="write" externalSource="NIKU" objectType="project" version="13.0"/>
<Projects>
<gel:include select="$project_node"/>
</Projects>
</NikuDataBus>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
<gel:set asString="false" select="$result/soapenv:Envelope/soapenv:Body/NikuDataBus/XOGOutput/Statistics" var="stats"/>
<gel:set asString="false" select="$stats/@failureRecords" var="failureRecs"/>
<core:if test="${failureRecs > 0}">
<gel:log level="ERROR">A Problem with the XOG happened.</gel:log>
<gel:log level="ERROR">Caught Exception was: <gel:expr select="$result"/>
</gel:log>
</core:if>
</core:when>
<core:otherwise>
<gel:log>No Tasks!</gel:log>
</core:otherwise>
</core:choose>
<!--Remove Idea Team-->
<!--First get the Idea Team Members -->
<sql:query escapeText="0"
var="ideaTeam"><![CDATA[
select srmr.unique_name, pt.team_uid
from inv_investments invi
join prteam pt on pt.prprojectid = invi.id
join srm_resources srmr on srmr.id = pt.prresourceid
where invi.code = ?]]><sql:param value="${idea_code}"/>
</sql:query>
<!--<core:if test="${ideaTeam.rowCount > 0}">-->
<core:choose>
<core:when test="${ideaTeam.rowCount > 0}">
<core:forEach items="${ideaTeam.rows}" trim="true" var="row1">
<gel:parse var="ideaTeamRemove">
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_idea.xsd">
<Header action="write" externalSource="NIKU" objectType="idea" version="8.0"/>
<Ideas>
<Idea active="false" name="${idea_name}" objectID="${idea_code}">
<InvestmentResources>
<Resource delete="true" resourceID="${row1.unique_name}" teamId="${row1.team_uid}"/>
</InvestmentResources>
</Idea>
</Ideas>
</NikuDataBus>
</gel:parse>
<!--Xog in the file now-->
<soap:invoke endpoint="Internal" var="ideaTeamRemove_OP">
<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="$ideaTeamRemove"/>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
<gel:set asString="true" select="$ideaTeamRemove_OP//XOGOutput/Status/@state" var="XOGState"/>
<gel:set asString="false" select="$ideaTeamRemove_OP/soapenv:Envelope/soapenv:Body/NikuDataBus/XOGOutput/Statistics" var="stats"/>
<gel:set asString="false" select="$stats/@failureRecords" var="failureRecs"/>
<core:if test="${XOGState == 'FAILURE'}">
<gel:log level="ERROR">A Problem with the XOG happened.</gel:log>
<gel:log level="ERROR">Caught Exception was: <gel:expr select="$ideaTeamRemove_OP"/>
</gel:log>
</core:if>
</core:forEach>
</core:when>
<core:otherwise>
<gel:parse var="ideaTeamRemove">
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_idea.xsd">
<Header action="write" externalSource="NIKU" objectType="idea" version="8.0"/>
<Ideas>
<Idea active="false" name="${idea_name}" objectID="${idea_code}"/>
</Ideas>
</NikuDataBus>
</gel:parse>
<!--Xog in the file now-->
<soap:invoke endpoint="Internal" var="ideaTeamRemove_OP">
<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="$ideaTeamRemove"/>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
<gel:set asString="true" select="$ideaTeamRemove_OP//XOGOutput/Status/@state" var="XOGState"/>
<gel:set asString="false" select="$ideaTeamRemove_OP/soapenv:Envelope/soapenv:Body/NikuDataBus/XOGOutput/Statistics" var="stats"/>
<gel:set asString="false" select="$stats/@failureRecords" var="failureRecs"/>
<core:if test="${XOGState == 'FAILURE'}">
<gel:log level="ERROR">A Problem with the XOG happened.</gel:log>
<gel:log level="ERROR">Caught Exception was: <gel:expr select="$ideaTeamRemove_OP"/>
</gel:log>
</core:if>
</core:otherwise>
</core:choose>
<!-- LOGOUT OF XOG -->
<soap:invoke endpoint="Internal" var="logout">
<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>
</gel:script>

 

Request your help in identifying and making the necessary changes in the script.

Outcomes