Clarity

  • 1.  Avail Start End Dates

    Posted Oct 19, 2018 06:06 AM

    #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.



  • 2.  Re: Avail Start End Dates

    Posted Oct 19, 2018 06:14 AM

    Sanjeeb,

     

    This appears to be a PPM question.  I just want to double check that you do not need assistance for an Agile Central piece of this before I move it to the PPM Community.


    Michael



  • 3.  Re: Avail Start End Dates

    Posted Oct 19, 2018 06:19 AM

    Sure Michael Please move the query to the PPM Community.