GELP! (Help with GEL)

[Anon Anon]

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>

[Atul.Kunkulol]

Just a thought - You don't have any value populated in p_ano and p_mes variable. Are they suppose to be NULL?

Have you tried to run the SQL Statement in SQL Developer or Toad to confirm that it is returning data?

and isnull(Horas.Incurridas,0)>0
and horas.ano = ${p_ano}
and horas.mes = ${p_mes}
and inv.code = 'PRJ-020719'

[Anon Anon]

I run it in MSSQL and got rows.
The values are passed in the step configuration.

[Atul.Kunkulol]

Have you tried to key in a record manually in that custom object and then XOG it out. Have a look at the output and validate if the partition and page layout have the same values which are in the process.

When you get "success" for runresult, do you get the number of inserted records or updated records count?

[Owen_R]

Hi
You say you get data when you run the query outside the script. I wonder if the section where you take the data rows and assign the outputs to GEL variables is correct?

I use this bit of code to do my variable assignment from a query result set - you don't need to have a line of code for each column of data if you do it this way, and any time you add or remove columns from the query it handles it automatically;

<core:forEach items="${v_query_1.rowsByIndex}" trim="true" var="v_query_1_data" >


<core:forEach var="v_query_1_field" items="${v_query_1.columnNames}" indexVar="idx" step="1">



<core:set value="${v_query_1_data[idx]}" var="${info_field}"/>


</core:forEach>

</core:forEach>

Edit - I usually use i instead of idx above but this was being interpreted as the code for italic font in this reply, so you might need to swap it back to i in your script.

When it comes to your XOG write, you can then refer to the variables directly like this:

<ColumnValue name="itau_project_code">${CodigoProyecto}</ColumnValue>

Try replacing line 115 to 128 in your code with my loop above and see if that helps.

[Anon Anon]

Thanks for your help!
Unfortunately I am still not being able to insert new custom objects instances. I followed your suggestions but it seems I am passing all values correctly.
I am getting SUCCESS in //Status/@state but getting 1 in //Statistics/@failureRecords. Is there any way to get a more detailed error message for those failure records?
thanks,

[Anon Anon]

b044113 wrote:

Is there any way to get a more detailed error message for those failure records?

The XOG response is all in your "runresult" variable (from which you are already extracting "XOG_STATUS" - the //Status/@state part).

So just dump the whole "runresult" to your output and see what it says;

<gel:out> <gel:expr select="$runresult"/> </gel:out>

and/or

<gel:log> <gel:expr select="$runresult"/> </gel:log>

[Anon Anon]

Dave wrote:

b044113 wrote:

Is there any way to get a more detailed error message for those failure records?

The XOG response is all in your "runresult" variable (from which you are already extracting "XOG_STATUS" - the //Status/@state part).

So just dump the whole "runresult" to your output and see what it says;

<gel:out> <gel:expr select="$runresult"/> </gel:out>

and/or

<gel:log> <gel:expr select="$runresult"/> </gel:log>

Thank you!
I tried that and I got:

<?xml version="1.0" encoding="UTF-8"?> <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"> <soapenv:Header/> <soapenv:Body> <XOGOutput xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/status.xsd"> <Object type="customObjectInstance"/> <Status state="SUCCESS"/> <Statistics failureRecords="1" insertedRecords="0" totalNumberOfRecords="1" updatedRecords="0"/> <Records> <Record> <KeyInformation> <column name="itau_horas_incurrida">PRJ-021179ROD1072792013</column> </KeyInformation> <ErrorInformation> <Severity>FATAL</Severity> <Description>com.niku.union.odf.exception.ODFServiceException: &lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;insertService type="xbl" componentId="odf" description="custom/customInsertService.xbl" transaction="dbTransaction" xmlns="http://schemas.niku.com/2002/objectTypes"/&gt; </Description> </ErrorInformation> </Record> </Records> </XOGOutput> </soapenv:Body> </soapenv:Envelope>

I checked permissions, but everything seems to be well configured. Any ideas of what could mean this Fatal Error?

[Deactivated User]

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"> <soapenv:Header/>
<soapenv:Body>
<XOGOutput xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/status.xsd">
<Object type="customObjectInstance"/>
<Status state="SUCCESS"/> <Statistics
failureRecords="1" insertedRecords="0" totalNumberOfRecords="1" updatedRecords="0"/>
<Records>
<Record>
<KeyInformation>
<column name="itau_horas_incurrida">PRJ-021179ROD1072792013</column>
</KeyInformation>
<ErrorInformation>
<Severity>FATAL</Severity>
<Description>com.niku.union.odf.exception.ODFServiceException: &lt;?xml version="1.0" encoding="UTF-8"?&gt; &lt;insertService type="xbl"
componentId="odf" description="custom/customInsertService.xbl"
transaction="dbTransaction" xmlns="http://schemas.niku.com/2002/objectTypes"/&gt;
</Description>
</ErrorInformation>
</Record> </Records>
</XOGOutput>
</soapenv:Body>
</soapenv:Envelope>


Just looking at the error

<column name="itau_horas_incurrida">PRJ-021179ROD1072792013</column>

looks as if that the value passed to the hours occured field is the project ID.
That is either the column name or the value don't sound right.

[Lowell Wetzel]

Hi Martin (another_martink) - I see that you ran into the same thing I ran into.  It turns out that Clarity 13.x has a problem writing "auto-number" ID/Name Using XOG.  I modified my script to use a date/time stamp with an incrementer so that I get a unique number for the time it is run.

Your usage of the -1 for the XOG statement worked in "previous" versions of Clarity.

Change your code to build a unique ID and take the Custom Subobject off of "auto-numbering" and it will fix the issue.  That's an ugly workaround if you also have users manually creating the subobject entries.  But.... it's the only way to get around this issue for now.

Thanks,

Lowell

[Anon Anon]

i know this is going to sound weird. Try changing your escapeText="false" to escapeText="0".

[Deactivated User]

Not that weird if you have compared the xml files and the xsd file for XOG on how they use boolean values.

Martti K.

[Anon Anon]

another_martink wrote:

Not that weird if you have compared the xml files and the xsd file for XOG on how they use boolean values.

Martti K.

Thanks,
I tried this but with no luck

[mansriva]

Try to write the XML which is being generated for XOG as input from your script and xog in that manualy andsee if that works, if it will al provide you that if the variables which are being used actualy holds values from your sql , yes or no.

Thanks,
Manish

[mansriva]

Try to write the XML which is being generated for XOG as input from your script and xog in that manualy andsee if that works, if it will al provide you that if the variables which are being used actualy holds values from your sql , yes or no.

Thanks,
Manish