Clarity PPM1

Expand all | Collapse all

create this process to automatically open / close the time indicators

Jump to Best Answer
  • 1.  create this process to automatically open / close the time indicators

    Posted 7 days ago

    Guys,
    how should I create this process to automatically open / close the time indicators.

    You must be allowed to set the number of periods to be open.

    For example, S-X and S + y, where S is a current week, X is a quantity of pre-current orders, and Y a number of later semantics that must remain open.


    Thanks


    Alexandre



  • 2.  RE: create this process to automatically open / close the time indicators

    Posted 6 days ago
    Hi Community - 
    Has anyone implemented a workflow using XOG to create and active the Time Reporting Periods as requested by Alexandre?
    Please jump in and help him out.


  • 3.  RE: create this process to automatically open / close the time indicators

    Posted 6 days ago
    Alexandre, you've said a few times in different threads that you're new to PPM. Some of the questions you're asking, and the solutions to those questions, require advanced knowledge of the system. I strongly recommend you engage a Clarity support partner, at least initially while you're upskilling.  I also recommend you purchase some training credits and do the requisite Clarity functional training courses to learn about the system. I took the training, and I learned HEAPS from the people on this forum who generously and patiently helped me. I am by no means an expert on SQL, GEL scripting, or process management and I am certainly not saying that the below is the best or even the right answer, but here is my answer:

    For your specific scenario above, I created an object called "Code Constants" to hold a bunch of information. One of the pieces of information was the number of time periods the client wanted to have open - past and future.  I then wrote a process that was scheduled to fire once a month to open future time periods, and close past ones.

    The work was done by a GEL script as follows:

    <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">
                            <!-- (C) Copyright TEA Consulting 2012 Ltd, May-2017 -->
                            <!-- Close Time Periods -->
                            <!--
    		******* Implementation
    
    		Created by       : 	(C) TEA Consulting 2012 Limited
    		Clarity Version  : 	15.2
    		Database Specific: 	Oracle
    		Description      : 	This script will manage time periods within an Administrator-defined window.
    
    		MODIFICATIONS 
    		[Date]			[Name]				[Change made and reason]
    		14-Nov-2017		Alistair Miller			Initially created.
    	-->
                            <!--
    		DEBUG Parameter
    			0 = Production
    			1 = Dev/Unit Testing
    			2 = User Testing
    	-->
                            <!-- Set GEL Variables -->
                            <!-- Error Conditions -->
                            <core:set value="" var="v_exception"/>
                            <core:set value="false" var="errorFlag"/>
                            <!-- XOG Login Permissions -->
                            <gel:parameter default="*******" var="v_xogUserId"/>
                            <gel:parameter default="***********" var="v_xogPwd"/>
                            <!-- Other Parameters and Variables -->
                            <gel:parameter default="1" var="DEBUG"/>
                            <core:set value="0" var="v_LeaveOpen"/>
                            <!-- Set the DB source for the whole script. -->
                            <gel:setDataSource dbId="Niku"/>
                            <!-- Query the CA PPM Database and return the current date and time -->
                            <core:if test="${errorFlag==false}">
                              <core:catch var="v_exception">
                                <sql:query var="v_sqlresults">
    				select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual
    			</sql:query>
                                <core:forEach items="${v_sqlresults.rowsByIndex}" var="row">
                                  <core:set value="${row[0]}" var="v_start"/>
                                </core:forEach>
                                <gel:log category="SQL" level="INFO">Migration commencing at:${v_start}</gel:log>
                              </core:catch>
                              <core:if test="${v_exception!=null}">
                                <gel:log category="SQL" level="ERROR">Unable to connect to CA PPM database: ${v_exception}</gel:log>
                                <core:set value="true" var="errorFlag"/>
                              </core:if>
                              <core:if test="${v_exception==null and DEBUG==1}">
                                <gel:log category="GEL" level="INFO">Successfully connected to CA PPM Database</gel:log>
                              </core:if>
                            </core:if>
                            <!-- Connect to the Db and find out the window of operation -->
                            <core:if test="${errorFlag == false}">
                              <core:catch var="v_exception">
                                <sql:query var="v_sqlquery">
    				select const_number from odf_ca_fon_code_constants where name = 'Time Periods To Manage'
    			</sql:query>
                              </core:catch>
                              <core:if test="${v_exception != null}">
                                <gel:log category="SQL" level="ERROR">Unable to retrieve value from Code Constants: ${v_exception}</gel:log>
                                <core:set value="true" var="errorFlag"/>
                              </core:if>
                              <core:if test="${v_exception == null}">
                                <core:forEach items="${v_sqlquery.rowsByIndex}" var="row">
                                  <core:set value="${row[0]}" var="v_LeaveOpen"/>
                                </core:forEach>
                                <core:if test="${DEBUG == 1}">
                                  <gel:log category="SQL" level="INFO">Time periods outside a ${v_LeaveOpen} month window will be closed </gel:log>
                                </core:if>
                              </core:if>
                            </core:if>
                            <!-- Connect to the Db and retrieve the list of open time periods more than n months old -->
                            <core:if test="${errorFlag==false}">
                              <core:catch var="v_exception">
                                <sql:query escapeText="false" var="v_sqlresults">
    			select to_char(prstart, 'YYYY-MM-DD'), to_char(prfinish, 'YYYY-MM-DD') , 'false' as status from prtimeperiod where prisopen = 1 and prfinish &lt; to_date(to_char(add_months(sysdate, -${v_LeaveOpen}), 'DDMMYYYY'), 'DDMMYYYY')
    			union all
    			select to_char(prstart, 'YYYY-MM-DD'), to_char(prfinish, 'YYYY-MM-DD') , 'true' as status from prtimeperiod where prisopen = 0 and prfinish between to_date(to_char(add_months(sysdate, -${v_LeaveOpen}), 'DDMMYYYY'), 'DDMMYYYY') and to_date(to_char(add_months(sysdate, ${v_LeaveOpen}), 'DDMMYYYY'), 'DDMMYYYY') 
    			union all
    			select to_char(prstart, 'YYYY-MM-DD'), to_char(prfinish, 'YYYY-MM-DD') , 'false' as status from prtimeperiod where prisopen = 1 and prfinish &gt; to_date(to_char(add_months(sysdate, ${v_LeaveOpen}), 'DDMMYYYY'), 'DDMMYYYY') 
    			</sql:query>
                              </core:catch>
                              <core:if test="${v_exception!=null}">
                                <gel:log category="SQL" level="ERROR">Unable to retrieve list of open Time Periods: ${v_exception}</gel:log>
                                <core:set value="true" var="errorFlag"/>
                              </core:if>
                              <core:if test="${v_exception==null}">
                                <core:set value="${v_sqlresults.rowCount}" var="v_numrecs"/>
                                <core:if test="${DEBUG==1}">
                                  <core:if test="${v_numrecs == 0}">
                                    <gel:log category="GEL" level="INFO">No time periods require action at this time</gel:log>
                                  </core:if>
                                  <core:if test="${v_numrecs ge 1}">
                                    <gel:log category="GEL" level="INFO">Successfully retrieved list of ${v_numrecs} time periods which require action from CA PPM Database</gel:log>
                                  </core:if>
                                </core:if>
                              </core:if>
                            </core:if>
                            <!-- Set up the XOG stub -->
                            <core:if test="${errorFlag == false}">
                              <core:if test="${v_numrecs gt 0}">
                                <gel:parse var="xogRootNode">
                                  <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_timeperiod.xsd">
                                    <Header action="write" externalSource="NIKU" objectType="timeperiod" version="15.2.0.0213"/>
                                    <TimePeriods/>
                                  </NikuDataBus>
                                </gel:parse>
                              </core:if>
                            </core:if>
                            <!-- Cycle the recordset and add them to the XOG -->
                            <core:if test="${errorFlag == false}">
                              <core:if test="${v_numrecs gt 0}">
                                <core:forEach items="${v_sqlresults.rowsByIndex}" var="row">
                                  <core:set value="${row[0]}T00:00:00" var="v_start"/>
                                  <core:set value="${row[1]}T00:00:00" var="v_finish"/>
                                  <core:set value="${row[2]}" var="v_action"/>
                                  <gel:parse var="xogTP">
                                    <TimePeriod create="true" finish="${v_finish}" openForTimeEntry="${v_action}" scale="weekly" start="${v_start}"/>
                                  </gel:parse>
                                  <gel:set insert="true" select="$xogRootNode//TimePeriods" value="${xogTP}"/>
                                </core:forEach>
                              </core:if>
                            </core:if>
                            <!-- Output the XOG message to the process log -->
                            <core:if test="${DEBUG ge 1}">
                              <core:if test="${v_numrecs gt 0}">
                                <gel:set asString="true" select="$xogRootNode/NikuDataBus" var="v_string"/>
                                <gel:log category="INPUT" level="INFO" message="${v_string}"/>
                              </core:if>
                            </core:if>
                            <!-- Log Into XOG -->
                            <core:if test="${errorFlag == false}">
                              <core:set value="internal" var="v_clarityURL"/>
                              <core:catch var="xogException">
                                <soap:invoke endpoint="${v_clarityURL}" var="auth">
                                  <soap:message>
                                    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
                                      <soapenv:Header/>
                                      <soapenv:Body>
                                        <xog:Login>
                                          <xog:Username>${v_xogUserId}</xog:Username>
                                          <xog:Password>${v_xogPwd}</xog:Password>
                                        </xog:Login>
                                      </soapenv:Body>
                                    </soapenv:Envelope>
                                  </soap:message>
                                </soap:invoke>
                              </core:catch>
                              <gel:set asString="true" select="$auth/soapenv:Envelope/soapenv:Body/xog:SessionID/text()" var="v_xogSessionId"/>
                              <core:if test="${v_xogSessionId == null}">
                                <gel:log category="XOG" level="WARN">Couldn't Log in to XOG. Check the username/password and CA PPM is running.</gel:log>
                                <core:set value="true" var="errorFlag"/>
                              </core:if>
                              <core:if test="${DEBUG > 0 and v_xogSessionId != null}">
                                <gel:log category="GEL" level="INFO">Retrieved Session ID: ${v_xogSessionId}</gel:log>
                              </core:if>
                            </core:if>
                            <!-- Execute the XOG -->
                            <core:if test="${errorFlag == false}">
                              <core:if test="${v_numrecs gt 0}">
                                <core:catch var="xog_error">
                                  <soap:invoke endpoint="${v_clarityURL}" 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>${v_xogSessionId}</xog:SessionID>
                                          </xog:Auth>
                                        </soapenv:Header>
                                        <soapenv:Body>
                                          <gel:include select="$xogRootNode/NikuDataBus"/>
                                        </soapenv:Body>
                                      </soapenv:Envelope>
                                    </soap:message>
                                  </soap:invoke>
                                </core:catch>
                                <!-- Check the XOG result -->
                                <gel:set select="$runresult//XOGOutput" var="xogResults"/>
                                <gel:set asString="true" select="$xogResults" var="xogReturn"/>
                                <core:if test="${DEBUG ge 1}">
                                  <gel:log category="XOG" level="INFO">XOG return message: ${xogReturn}</gel:log>
                                </core:if>
                                <gel:set asString="true" select="$xogResults/Status/@state" var="xogOutcome"/>
                                <gel:forEach select="$xogResults/Records" var="v_record">
                                  <gel:set asString="true" select="$v_record/Record" var="XOGoutputPrecords"/>
                                  <core:if test="${XOGoutputPrecords != null}">
                                    <gel:log category="XOG" level="WARN">XOG Return Message: ${xogReturn}</gel:log>
                                  </core:if>
                                </gel:forEach>
                              </core:if>
                            </core:if>
                            <!-- Connect to the Db and find out the date of the last time period -->
                            <!-- This tells us whether we need to create new ones -->
                            <core:if test="${errorFlag==false}">
                              <core:catch var="v_exception">
                                <sql:query var="v_sqlresults">
    				select max(to_char(prfinish, 'YYYY-MM-DD')), to_char(add_months(sysdate, 12), 'YYYY-MM-DD') from prtimeperiod
    			</sql:query>
                                <core:forEach items="${v_sqlresults.rowsByIndex}" var="row">
                                  <core:set value="${row[0]}" var="v_last"/>
                                  <core:set value="${row[1]}" var="v_until"/>
                                </core:forEach>
                                <gel:log category="SQL" level="INFO">Last existing time period is ${v_last}. Creating new time periods out until ${v_until}</gel:log>
                              </core:catch>
                              <core:if test="${v_exception!=null}">
                                <gel:log category="SQL" level="ERROR">Unable to connect to CA PPM database: ${v_exception}</gel:log>
                                <core:set value="true" var="errorFlag"/>
                              </core:if>
                              <core:if test="${v_exception==null and DEBUG==1}">
                                <gel:log category="GEL" level="INFO">Successfully connected to CA PPM Database</gel:log>
                              </core:if>
                            </core:if>
                            <!-- If the date of the final time reporting period is less than 12 months into the future, we need to create more -->
                            <core:if test="${errorFlag == false}">
                              <core:if test="${v_last lt v_until}">
                                <!-- Set up the conditions to add required time periods to the XOG -->
                                <core:set value="${v_last}" var="v_start"/>
                                <core:while test="${v_start lt v_until}">
                                  <sql:query var="v_sqlcycle">
    					select to_char(to_date('${v_start}', 'YYYY-MM-DD')+7, 'YYYY-MM-DD') from dual
    				</sql:query>
                                  <core:forEach items="${v_sqlcycle.rowsByIndex}" var="row">
                                    <core:set value="${row[0]}" var="finishdate"/>
                                  </core:forEach>
                                  <!-- Add the record to the XOG -->
                                  <core:set value="${v_start}T00:00:00" var="periodstart"/>
                                  <core:set value="${finishdate}T00:00:00" var="periodfinish"/>
                                  <!-- Set up XOG stub -->
                                  <gel:parse var="xogRootNode">
                                    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_timeperiod.xsd">
                                      <Header action="write" externalSource="NIKU" objectType="timeperiod" version="15.2.0.0213"/>
                                      <TimePeriods>
                                        <TimePeriod create="true" finish="${periodfinish}" openForTimeEntry="false" scale="weekly" start="${periodstart}"/>
                                      </TimePeriods>
                                    </NikuDataBus>
                                  </gel:parse>
                                  <!-- Output the XOG message to the process log -->
                                  <core:if test="${DEBUG ge 1}">
                                    <gel:set asString="true" select="$xogRootNode/NikuDataBus" var="v_string"/>
                                    <gel:log category="INPUT" level="INFO" message="${v_string}"/>
                                  </core:if>
                                  <!-- Execute the XOG -->
                                  <core:catch var="xog_error">
                                    <soap:invoke endpoint="${v_clarityURL}" 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>${v_xogSessionId}</xog:SessionID>
                                            </xog:Auth>
                                          </soapenv:Header>
                                          <soapenv:Body>
                                            <gel:include select="$xogRootNode/NikuDataBus"/>
                                          </soapenv:Body>
                                        </soapenv:Envelope>
                                      </soap:message>
                                    </soap:invoke>
                                  </core:catch>
                                  <!-- Check the XOG result -->
                                  <core:if test="${xog_error != null}">
                                    <gel:log>Error: ${xog_error}</gel:log>
                                  </core:if>
                                  <core:if test="${xog_error == null}">
                                    <gel:set select="$runresult//XOGOutput" var="xogResults"/>
                                    <gel:set asString="true" select="$xogResults" var="xogReturn"/>
                                    <core:if test="${DEBUG ge 1}">
                                      <gel:log category="XOG" level="INFO">XOG return message: ${xogReturn}</gel:log>
                                    </core:if>
                                    <gel:set asString="true" select="$xogResults/Status/@state" var="xogOutcome"/>
                                    <gel:forEach select="$xogResults/Records" var="v_record">
                                      <gel:set asString="true" select="$v_record/Record" var="XOGoutputPrecords"/>
                                      <core:if test="${XOGoutputPrecords != null}">
                                        <gel:log category="XOG" level="WARN">XOG Return Message: ${xogReturn}</gel:log>
                                      </core:if>
                                    </gel:forEach>
                                  </core:if>
                                  <core:set value="${finishdate}" var="v_start"/>
                                </core:while>
                              </core:if>
                            </core:if>
                            <!-- XOG Log out -->
                            <core:if test="${errorFlag == false}">
                              <core:if test="${v_numrecs gt 0}">
                                <soap:invoke endpoint="${v_clarityURL}" 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>${v_xogSessionId}</xog:SessionID>
                                        </xog:Auth>
                                      </soapenv:Header>
                                      <soapenv:Body>
                                        <xog:Logout/>
                                      </soapenv:Body>
                                    </soapenv:Envelope>
                                  </soap:message>
                                </soap:invoke>
                              </core:if>
                            </core:if>
                            <!-- Get the current date and time -->
                            <gel:formatDate format="yyyy'-'MM'-'dd' 'HH':'mm':'SS" stringVar="v_sysDate"/>
                            <gel:log category="XOG" level="INFO">Process concluded at ${v_sysDate}</gel:log>
                          </gel:script>

    Keep asking, keep searching, keep learning.
    Alistair


  • 4.  RE: create this process to automatically open / close the time indicators

    Posted 6 days ago
    @Alistair Miller
    I'm seeing with the consultancy the best way, but I have already made the request for trainings and it is in the approval phase.
    When that is not enough I'm trying to get knowledge from all sides.
    And thank you very much.

    And I'll see what I can do with this information you gave me.

    Alexandre





  • 5.  RE: create this process to automatically open / close the time indicators
    Best Answer

    Posted 5 days ago
    Opening and closing time periods is very simple manual task - would take an administrator about a minute to do.

    You should not be scared by writing a "process" which is a "manual process" - i.e. a scripted manual steps in the application. Implementing this as a monthly or weekly task for your administration team to perform would be a very simple thing to do and so you can have implemented your "process" with minimal effort.

    A technical solution is also possible of course ; but how much time are you going to spend understanding the technical things you need to know to implement such a thing (i.e. learn GEL and XOG) let alone the time you are going to spend testing a technical solution so you are satisfied it works.

    Sometimes the simple-solution is much much better than the clever-technical solution.


  • 6.  RE: create this process to automatically open / close the time indicators

    Posted 3 days ago
    Right as always, Dave.  We went down the automation route because we wanted to automate as much of the routine maintenance as possible. We had an outsource support partner that was pretty regularly either 'forgetting' to do them, or making a hash of them (our time periods went from Mon-Sun to Sun-Sat at one point, for absolutely no reason other than the support person couldn't read a calendar).  We didn't have spare personnel in the PMO to either monitor these activities or do them themselves, and so we decided to automate.