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">
<core:set value="" var="v_exception"/>
<core:set value="false" var="errorFlag"/>
<gel:parameter default="*******" var="v_xogUserId"/>
<gel:parameter default="***********" var="v_xogPwd"/>
<gel:parameter default="1" var="DEBUG"/>
<core:set value="0" var="v_LeaveOpen"/>
<gel:setDataSource dbId="Niku"/>
<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>
<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>
<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 < 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 > 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>
<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>
<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>
<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>
<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>
<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>
<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>
<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>
<core:if test="${errorFlag == false}">
<core:if test="${v_last lt v_until}">
<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>
<core:set value="${v_start}T00:00:00" var="periodstart"/>
<core:set value="${finishdate}T00:00:00" var="periodfinish"/>
<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>
<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>
<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>
<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>
<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>
<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
Original Message:
Sent: 10-09-2019 01:40 PM
From: Alexandre Gavazzi
Subject: create this process to automatically open / close the time indicators
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