AnsweredAssumed Answered

GELP! (Help with GEL)

Question asked by b044113 on Oct 3, 2013
Latest reply on Oct 31, 2013 by Lowell
Hi,
I have created a custom object in order to have some summarized data around actual and planned hours. I am trying to run a GEL Script within a process. The parsing is OK and it seems that the process runs successfully (The log does not throwns any error and the $runresult//Status/@state value is SUCCESS) , but the object instances are not inserted in the cutom object table in the DB. Since this is my first script I am starting with an object with no so many fields. The table has a few more non mandatory fields. Could you help me?
thanks,

The script is the following (sorry, is in spanish):
<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:xog="http://www.niku.com/xog"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <!-- DATASOURCE -->
    <!-- ========== -->
    <gel:setDataSource dbId="niku"/>

    <!-- PARAMETERS -->
    <!-- ========== -->
    <gel:parameter default="http://localhost" var="p_XOGURL"/>
    <gel:parameter default="" var="p_XOGUsername"/>
    <gel:parameter default="" secure="true" var="p_XOGPassword"/>
    <gel:parameter default="" var="p_horas_incurridas"/>    
    <gel:parameter default="" var="p_mes"/>
    <gel:parameter default="" var="p_ano"/>
    
    <gel:out>[XOG] Arranca proceso</gel:out>
    <gel:log category="XOG" level="INFO">[XOG] Arranca proceso</gel:log>


    <!-- QUERY EXECUTION -->
    <!-- ========================================== -->
    <sql:query escapeText="false" var="v_query_1"><![CDATA[
                SELECT 
                       CASE WHEN PROG.code is null THEN inv.code ELSE PROG.code END CodigoProyecto,
                       CASE WHEN PROG.name is null THEN inv.name ELSE PROG.name END NombreProyecto,
                       L1.name LineaServicio,
                       RES.unique_name CodigoRecurso, 
                       RES.full_name NombreRecurso,
                       ROL.full_name Rol,
                       L3.name NombreGerencia,
                       convert(varchar,Horas.mes) Mes,
                       convert(varchar,Horas.ano) Ano,
                       SUM(Horas.Incurridas) Horas
                                                 
                FROM niku.INV_INVESTMENTS INV 
                LEFT OUTER JOIN (SELECT SUM(TE_SLC.slice) incurridas, TSK.prprojectid, ASG.prresourceid, 
                                        YEAR(TE_SLC.slice_date) Ano, MONTH(TE_SLC.slice_date) Mes
                                 FROM niku.PRTASK TSK 
                                 LEFT OUTER JOIN niku.PRASSIGNMENT ASG on TSK.prid = ASG.prtaskid 
                                 LEFT OUTER JOIN niku.PRTIMEENTRY TE on ASG.prid = TE.prassignmentid 
                                 LEFT OUTER JOIN niku.PRJ_BLB_SLICES TE_SLC on (TE.prid = TE_SLC.prj_object_id 
                                                                            and TE_SLC.slice_request_id = 5001001) 




 
                                 WHERE TE_SLC.slice >0 and 
                                       TE.prid IN (SELECT distinct TENTRY.prid 
                                                   FROM niku.PRTIMEENTRY TENTRY, niku.PRTIMESHEET TSHEET 
                                                   WHERE TENTRY.prtimesheetid = TSHEET.prid 
                                                     and TSHEET.prstatus = 4 ) 
                       
                                 GROUP BY TSK.prprojectid, ASG.prresourceid, YEAR(TE_SLC.slice_date), MONTH(TE_SLC.slice_date)
                                 ) Horas on INV.id = Horas.prprojectid 

                --Joineo para obtener la información de programas
                LEFT OUTER JOIN (SELECT fla.child_id, inv4.code, inv4.name 
                                 FROM niku.inv_flat_hierarchies fla 
                                 INNER JOIN niku.INV_INVESTMENTS inv4 on fla.parent_id = inv4.id
                                 WHERE fla.parent_id <> fla.child_id
                                ) PROG ON PROG.child_id = inv.id 

                --Join para obtener información de recursos
                LEFT OUTER JOIN niku.SRM_RESOURCES RES ON Horas.prresourceid = RES.id 
                
                --Join para obtener información de roles
                LEFT OUTER JOIN niku.PRJ_RESOURCES RESROL on RESROL.prid = RES.ID 
                LEFT OUTER JOIN niku.SRM_RESOURCES ROL on RESROL.prprimaryroleid = ROL.id 
                             
                -- Joineamos con las dos tablas de abajo para obtener la línea de servicio del proyecto
                LEFT OUTER JOIN niku.ODF_CA_PROJECT INV3 on (INV.id = INV3.id 
                                                         and INV.odf_object_code = 'project') 
                LEFT OUTER JOIN niku.CMN_LOOKUPS_V L1 on (L1.lookup_type = 'ITAU_LINEA_SERVICIO' 
                                       and INV3.itau_linea_servicio = L1.lookup_code 
                                       and L1.language_code = 'es')      

                -- Joineamos para obtener la gerencia
                LEFT OUTER JOIN (SELECT A.record_id, D2.itau_gerencia_rec 
                                 FROM niku.PRJ_OBS_ASSOCIATIONS A, niku.DEPARTMENTS D, niku.ODF_CA_DEPARTMENT D2 
                                 WHERE A.table_name = 'SRM_RESOURCES' 
                                   and A.unit_id = D.obs_unit_id  
                                   and D.id = D2.id
                                ) DEP_REC on RES.id = DEP_REC.record_id 
                LEFT OUTER JOIN niku.CMN_LOOKUPS_V L3  on (L3.lookup_type = 'ITAU_GERENCIAS_REC' 
                                                       and DEP_REC.itau_gerencia_rec = L3.lookup_enum 
                                                       and L3.language_code = 'es')
                                                                                                                                                
                WHERE  RESROL.prisrole = 0
                   and L1.name in ('Proyecto Pequeño', 'Proyecto Grande', 'Operaciones Especiales')
                   and ROL.full_name in ('Analista Técnico Programador', 'Analista de Sistemas', 'Proveedor', 'Operaciones Especiales')
                   and isnull(Horas.Incurridas,0)>0
                   and horas.ano = ${p_ano}
                   and horas.mes = ${p_mes}




   and inv.code = 'PRJ-020719'
 
                GROUP  BY 
                   CASE WHEN PROG.code is null THEN inv.code ELSE PROG.code END,
                   CASE WHEN PROG.name is null THEN inv.name ELSE PROG.name END,
                   L1.name,
                   Horas.mes,
                   Horas.ano,
                   RES.unique_name, 
                   RES.full_name,
                   L3.name,
                   ROL.full_name
             ]]>
    </sql:query>

    <!-- FOR EACH ROW A NEW INSTANCE IS CREATED -->
    <!-- ======================================= -->
    <core:forEach items="${v_query_1.rows}" var="row">
      

    <!-- VARIABLES ASSIGNMENT -->
        <core:set var="v_CodigoProyecto">${row.CodigoProyecto}</core:set>
        <core:set var="v_NombreProyecto">${row.NombreProyecto}</core:set>
        <core:set var="v_CodigoRecurso">${row.CodigoRecurso}</core:set>
        <core:set var="v_NombreRecurso">${row.NombreRecurso}</core:set>
        <core:set var="v_Rol">${row.Rol}</core:set>
        <core:set var="v_NombreGerencia">${row.NombreGerencia}</core:set>
        <core:set var="v_LineaServicio">${row.LineaServicio}</core:set>
        <core:set var="v_Mes">${row.Mes}</core:set>
        <core:set var="v_Ano">${row.Ano}</core:set>
        <core:set var="v_Horas">${row.Horas}</core:set>


 
        <!-- OBJECT INSTANCE CREATION -->
        <gel:parse var="xogwrite">
            <NikuDataBus
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">
                <Header action="write" externalSource="NIKU"
                       objectType="customObjectInstance" version="8.0"/>
                <customObjectInstances objectCode="itau_act_horas_incurridas">
                    <instance instanceCode="-1" objectCode="itau_act_horas_incurridas">
                        <CustomInformation>

  
                    <ColumnValue name="code">AUTO-CODE</ColumnValue>
                            <ColumnValue name="name">Incurrido</ColumnValue>
                            <ColumnValue name="page_layout">odf.chg_importFrame</ColumnValue>
                            <ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
                            <ColumnValue name="itau_project_code">${row.CodigoProyecto}</ColumnValue>
                            <ColumnValue name="itau_project_name">${row.NombreProyecto}</ColumnValue>
                            <ColumnValue name="itau_resource_code">${row.CodigoRecurso}</ColumnValue>
                            <ColumnValue name="itau_nombre_recurso">${row.NombreRecurso}</ColumnValue>
                            <ColumnValue name="itau_rol">${row.Rol}</ColumnValue>
                            <ColumnValue name="itau_nombre_gerencia">${row.NombreGerencia}</ColumnValue>
                            <ColumnValue name="itau_linea_servicio">${row.LineaServicio}</ColumnValue>
                            <ColumnValue name="itau_mes">${row.Mes}</ColumnValue>
                            <ColumnValue name="itau_ano">${row.Ano}</ColumnValue>
                            <ColumnValue name="itau_horas_incurrida">${row.Horas}</ColumnValue>
                        </CustomInformation>
                    </instance>
                </customObjectInstances>
            </NikuDataBus>
        </gel:parse>
        


<gel:out>[XOG] XML root parsed</gel:out>
        <gel:log category="XOG" level="INFO">[XOG] XML root parsed</gel:log>

 
 
        <!-- LOGIN -->
        <!-- ========== -->
        <core:catch var="v_xog_exception">
            <soap:invoke endpoint="${p_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>${p_XOGUsername}</xog:Username>
                                <xog:Password>${p_XOGPassword}</xog:Password>
                            </xog:Login>
                        </soapenv:Body>
                    </soapenv:Envelope>
                </soap:message>
            </soap:invoke>
            



<gel:set asString="true" select="$auth/soapenv:Envelope/soapenv:Body/xog:SessionID/text()" var="sessionID"/>





            <core:choose>
                <core:when test="${sessionID == null}">
                    <gel:out>[XOG]Couldn't Log in to XOG. Check the username/password and Application has started.</gel:out>
                    <gel:log category="XOG" level="ERROR">Couldn't Log in to XOG. Check the username/password and Application has started.</gel:log>
                </core:when>

                <core:otherwise>
                    <gel:out>[XOG]Log in successful ${sessionID}</gel:out>
                    <gel:log category="XOG" level="INFO">[XOG]Log in successful ${sessionID}</gel:log>

                    <!-- WRITE OBECT -->
                    <!-- =========== -->          
                    <core:catch var="v_xog_exception">
                        <soap:invoke endpoint="${p_XOGURL}/niku/xog" 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>${sessionID}</xog:SessionID>
                                        </xog:Auth>
                                    </soapenv:Header>
                                    <soapenv:Body>
                                        <gel:include select="$xogwrite"/>
                                    </soapenv:Body>
                                </soapenv:Envelope>
                            </soap:message>
                        </soap:invoke>
                    </core:catch>



    <!-- CHECK IF EVERYTHING OK -->
                    <!-- ====================== -->
                    <core:choose>
                        <core:when test="${v_xog_exception != null}">
                            <gel:out>[XOG]XOG operation failed: ${v_xog_exception}</gel:out>
                            <gel:log category="XOG" level="ERROR">XOG operation failed: ${v_xog_exception2}</gel:log>
                        </core:when>
                    </core:choose>

                    <gel:set asString="true" select="$runresult//Status/@state" var="XOG_STATUS"/>
                    <gel:out>[XOG]Estado: ${XOG_STATUS}</gel:out>
                    <gel:log category="XOG" level="INFO">[XOG]Estado: ${XOG_STATUS}</gel:log>


           




    <!-- LOGOUT -->

                <soap:invoke endpoint="${p_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>${sessionID}</xog:SessionID>
                                    </xog:Auth>
                                </soapenv:Header>
                                <soapenv:Body>
                                    <xog:Logout/>
                                </soapenv:Body>
                            </soapenv:Envelope>
                        </soap:message>
                    </soap:invoke>











<gel:out>[XOG]Logged out </gel:out>
                    <gel:log category="XOG" level="INFO">Logged out </gel:log>



    </core:otherwise>
            </core:choose>
        </core:catch>


<core:if test="${v_xog_exception != null}">
            <gel:out>XOG failed: ${v_xog_exception}</gel:out>
            <gel:log category="XOG" level="ERROR">XOG failed: ${v_xog_exception}</gel:log>
        </core:if>



    </core:forEach>
</gel:script>

Outcomes