AnsweredAssumed Answered

Change "Actual Thru" in task

Question asked by cris.perezcoll on Jul 7, 2011
Latest reply on Jul 14, 2011 by cris.perezcoll
Hi,
I nedd our help. I create a process in gel script (see: I have this job.doc). In apart of a process that inputs actuals based on the progress of the tasks. This script checks all the tasks on the system and sets actuals based on the progress and when there is no progress inputs 0 actuals.On these cases the actuals set are 0 and as a consequence an actthru date is also set.
I would like to remove this date when the actuals is "0" or make a condition in progress to no update the actuals when it's "0".

This job did a subcontractor and now we have found this error. I'm have not experience in jobs and gel script and i need our help. I send the procces and the action with datails
How and when can i make these changes? i sent the part on gel script that make de update in actuals: action_actualizar avance tareas.txt

<?xml version="1.0" encoding="UTF-8"?>
<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="jelly:sql"
xmlns:xog="http://www.niku.com/xog"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


<!-- Iniciar el origen de datos-->
<gel:setDataSource dbId="Niku"/>

<!-- Obtener el ID de la tarea -->
<core:set var="v_projid" value="${gel_objectInstanceId}"/>

<sql:update var="insert">
UPDATE CLARITY.ODF_CA_TASK T
SET T.SM_HITOFINALIZADO=1
WHERE T.ID IN (SELECT T2.PRID
FROM CLARITY.PRTASK T2
WHERE T2.PRPROJECTID=${gel_objectInstanceId}
AND T2.PRISMILESTONE=1
AND T2.PRSTATUS=2
AND T2.PRPCTCOMPLETE=1)
</sql:update>


<sql:update var="insert" escapeText="false">
<![CDATA[UPDATE CLARITY.PRTASK T
SET T.PRPCTCOMPLETE=1
  WHERE T.PRPCTCOMPLETE>1
        AND T.PRPROJECTID=${gel_objectInstanceId}]]>
</sql:update>


<!-- Obtenemos la informacion del proyecto y de la tarea asociada -->
<sql:query var="v_projResult">
SELECT inv.CODE, inv.NAME
FROM CLARITY.INV_INVESTMENTS inv
WHERE inv.ID = '${v_projid}'
</sql:query>

<core:choose>
<core:when test="${v_projResult.rowCount == 0}">
<gel:log level="ERROR">No hay proyecto asociado al ID: ${v_projid}</gel:log>
</core:when>
<core:otherwise>
<core:forEach items="${v_projResult.rowsByIndex}" var="v_projRow">
<core:set var="v_proyectocode" value="${v_projRow[0]}"/>
<core:set var="v_proyectoname" value="${v_projRow[1]}"/>
</core:forEach>
</core:otherwise>
</core:choose>


<!-- Cargamos el xml de lectura de proyectos -->
<gel:parse var="v_xml_readproj">
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
<Header version="6.0.11" action="read" objectType="project" externalSource="NIKU">
<!-- 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="true"/>
<args name="include_dependencies" value="true"/>
<args name="include_subprojects" value="true"/>
<args name="include_resources" value="true"/>
<args name="include_baselines" value="true"/>
<args name="include_allocations" value="true"/>
<args name="include_estimates" value="true"/>
<args name="include_actuals" value="true"/>
<args name="include_custom" value="true"/>
</Header>
<Query>
<Filter name="projectID" criteria="EQUALS">${v_proyectocode}</Filter>
</Query>
</NikuDataBus>
</gel:parse>

<!-- Haciendo login en XOG -->
<core:set var="v_XOGURL" value="http://localhost" />
<soap:invoke endpoint="${v_XOGURL}/niku/xog" 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>xxxx</xog:Username>
<xog:Password>xxxx</xog:Password>
</xog:Login>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
<gel:set asString="true" select="$auth/SOAP-ENV:Envelope/SOAP-ENV:Body/xog:SessionID/text()" var="v_sessionID" />
<!--
<gel:out>Sesion: ${v_sessionID}</gel:out>
-->
<!-- Ejecutamos XOG de lectura de proyecto con la sesion anterior paasandole el stream de XML que hemos generado en v_xml_taskroot, en el nodo raiz NkuDataBus -->
<soap:invoke endpoint="${v_XOGURL}/niku/xog" var="v_xog_proy">
<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_sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<gel:include select="$v_xml_readproj" />
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
<gel:serialize fileName="C:/niku/OPDE_Xog/proy_orig.xml" var="${v_xog_proy}"/>

<!-- Obtenemos la informacion del proyecto y de la tarea asociada -->
<sql:query var="v_tareaResult">



SELECT tarea.PRNAME, tarea.PRPCTCOMPLETE, tarea.PREXTERNALID, tarea.PRID



FROM CLARITY.PRTASK tarea, CLARITY.PRASSIGNMENT A



WHERE tarea.PRPROJECTID ='${v_projid}'




AND TAREA.PRID=A.PRTASKID
AND TAREA.PRISMILESTONE=0
</sql:query>

<core:choose>
<core:when test="${v_tareaResult.rowCount == 0}">
<gel:log level="ERROR">No hay proyecto asociado a la tarea: ${v_tareaid}</gel:log>
</core:when>
<core:otherwise>
<core:forEach items="${v_tareaResult.rowsByIndex}" var="v_tareaRow">

<core:set var="v_tareaname" value="${v_tareaRow[0]}"/>
<core:set var="v_tareaperccomp" value="${v_tareaRow[1]}"/>
<core:set var="v_tareaextid" value="${v_tareaRow[2]}"/>











<core:set var="v_tareaid" value="${v_tareaRow[3]}"/>















































<!-- Obtenemos la fecha de fin estimada para el porcentage de trabajo introducido -->








<sql:query var="v_fechaResult">








SELECT TO_CHAR(CLARITY.SM_FCT_FINISHDATE(${v_tareaid}),'YYYY-MM-DD')||'T'||'15:00:00' FROM dual








</sql:query>









<core:choose>












<core:when test="${v_fechaResult.rowCount == 0}">















<gel:log level="WARNING">No hay fecha de Fin estimada</gel:log>












</core:when>












<core:otherwise>















<core:forEach items="${v_fechaResult.rowsByIndex}" var="v_fechaRow">



















<core:set var="v_fechafin" value="${v_fechaRow[0]}"/>















</core:forEach>












</core:otherwise>








</core:choose>















































<!-- Obtenemos todas las asignaciones de la tarea y quienes las han hecho, hacemos una busqueda









en el xml e introducimos el segmento generado anteriormente en todos ellos -->









<!-- Obtenemos la informacion del proyecto y de la tarea asociada -->








<sql:query var="v_asigResult">








SELECT REC.UNIQUE_NAME, ROUND((ASIG.PRESTSUM + ASIG.PRACTSUM)*TASK.PRPCTCOMPLETE/3600,2),












((ASIG.PRESTSUM + ASIG.PRACTSUM)/3600) - ROUND((ASIG.PRESTSUM + ASIG.PRACTSUM)*TASK.PRPCTCOMPLETE/3600,2),












(TO_CHAR(TASK.PRFINISH + 1,'YYYY-MM-DD')||'T00:00:00'),
(TO_CHAR(TASK.PRSTART,'YYYY-MM-DD')||'T07:30:00')









FROM CLARITY.PRASSIGNMENT asig, CLARITY.SRM_RESOURCES rec, CLARITY.PRJ_RESOURCES ROLE, CLARITY.PRTASK TASK











WHERE ASIG.PRRESOURCEID = ROLE.PRID AND












ROLE.PRID = REC.ID AND












--ROLE.PRISROLE = 0 AND












REC.RESOURCE_TYPE = 0 AND












ASIG.PRTASKID = ${v_tareaid} AND












ASIG.PRTASKID = TASK.PRID








</sql:query>









<core:choose>












<core:when test="${v_asigResult.rowCount == 0}">















<gel:log level="WARNING">No recupera nada de la asignacion</gel:log>












</core:when>












<core:otherwise>















<core:forEach items="${v_asigResult.rowsByIndex}" var="v_asigRow">



















<core:set var="v_recname" value="${v_asigRow[0]}"/>



















<core:set var="v_asigact" value="${v_asigRow[1]}"/>



















<core:set var="v_asigest" value="${v_asigRow[2]}"/>



















<core:set var="v_taskfinish" value="${v_asigRow[3]}"/>



















<core:set var="v_taskstart" value="${v_asigRow[4]}"/>








<!--















<gel:out>Recurso: ${v_recname}</gel:out>















<gel:out>Actual: ${v_asigact}</gel:out>















<gel:out>Pendiente: ${v_asigest}</gel:out>








-->















<!-- Generamos los segmentos de estimado y actual que luego incluiremos en todas las asignaciones o tasklabor de la tarea -->















<gel:parse var="v_xml_segact">















<ActCurve>















<Segment finish="${v_fechafin}" start="${v_taskstart}" sum="${v_asigact}"/>















</ActCurve>















</gel:parse>
















<gel:parse var="v_xml_segest">















<EstCurve>















<Segment finish="${v_taskfinish}" start="${v_taskstart}" sum="${v_asigest}"/>















</EstCurve>















</gel:parse>

















<!-- Introducimos el segmento en la asignacion-->

















<core:catch var="exception1">















<gel:set value="${v_xml_segact}" select="$v_xog_proy/soapenv:Envelope/soapenv:Body/NikuDataBus/Projects/Project/Tasks/Task[@internalTaskID=$v_tareaid]/Assignments/TaskLabor[@resourceID=$v_recname]/ActCurve"/>















</core:catch>
































<core:if test="${exception1 !=null}">















<gel:log level="WARNING">Recurso: ${v_recname} no est?n ${v_tareaid} (ActCurve)</gel:log>















</core:if>































<core:catch var="exception2">















<gel:set value="${v_xml_segest}" select="$v_xog_proy/soapenv:Envelope/soapenv:Body/NikuDataBus/Projects/Project/Tasks/Task[@internalTaskID=$v_tareaid]/Assignments/TaskLabor[@resourceID=$v_recname]/EstCurve"/>















</core:catch>































<core:if test="${exception2 !=null}">















<gel:log level="WARNING">Recurso: ${v_recname} no est?n ${v_tareaid} (EstCurve)</gel:log>















</core:if>



















<core:catch var="exception3">















<gel:set value="${v_asigact}" select="$v_xog_proy/soapenv:Envelope/soapenv:Body/NikuDataBus/Projects/Project/Tasks/Task[@internalTaskID=$v_tareaid]/Assignments/TaskLabor[@resourceID=$v_recname]/@actualWork"/>















</core:catch>































<core:if test="${exception3 !=null}">















<gel:log level="WARNING">Recurso: ${v_recname} no est?n ${v_tareaid} (actualWork)</gel:log>















</core:if>































<core:catch var="exception4">















<gel:set value="${v_asigest}" select="$v_xog_proy/soapenv:Envelope/soapenv:Body/NikuDataBus/Projects/Project/Tasks/Task[@internalTaskID=$v_tareaid]/Assignments/TaskLabor[@resourceID=$v_recname]/@remainingWork"/>















</core:catch>















<core:if test="${exception4 !=null}">















<gel:log level="WARNING">Recurso: ${v_recname} no est?n ${v_tareaid} (remainingWork)</gel:log>















</core:if>








<!--















<gel:set select="$v_xog_proy/soapenv:Envelope/soapenv:Body/NikuDataBus/Projects/Project/Tasks/Task[@internalTaskID=$v_tareaid]/Assignments/TaskLabor[@resourceID=$v_recname]" var="v_hola" asString="true"/>















<gel:out>${v_hola}</gel:out>








-->















</core:forEach>












</core:otherwise>








</core:choose>






































</core:forEach>
</core:otherwise>
</core:choose>





<gel:serialize fileName="C:/niku/OPDE_Xog/proyecto_mod.xml" var="${v_xog_proy}"/>

<!-- Ejecutamos XOG de escritura de proyecto con la sesion anterior paasandole el stream de XML que hemos generado en v_xml_taskroot, en el nodo raiz NkuDataBus
-->
<soap:invoke endpoint="${v_XOGURL}/niku/xog" var="v_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>${v_sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<gel:include select="$v_xog_proy/soapenv:Envelope/soapenv:Body/NikuDataBus" />
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>

<gel:serialize fileName="C:/niku/OPDE_Xog/error.xml" var="${v_result}"/>

<!-- Log out of the XOG
-->
<soap:invoke endpoint="${v_XOGURL}/niku/xog" 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_sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<xog:Logout />
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>


<sql:query var="varResult1">
SELECT DISTINCT V.ODF_PK
FROM CLARITY.ODF_TASK_V V
WHERE V.PROJECT_CODE = '${v_proyectocode}' AND V.PRISTASK = 1 AND V.PRACTSUM = 0


</sql:query>




<core:forEach items="${varResult1.rowsByIndex}" var="varRow1">




<core:set var="v_tareaid" value="${varRow1[0]}" />



<sql:update var="insert">



UPDATE PRTASK



SET PRSTATUS = 0



WHERE PRID = '${v_tareaid}'


</sql:update>
</core:forEach>

</gel:script>
Thank you veru much

Outcomes