Clarity

Expand all | Collapse all

XOG in Custom Object Instance - Lookup Error

  • 1.  XOG in Custom Object Instance - Lookup Error

    Posted Nov 24, 2010 07:50 AM
    I'm building up a script to create new instances of a project sub-object (v12.06). Before I start putting in dynamic processing I'm starting with a simple single write call with hard coded values as per the script below:
    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">
        <Header action="write" externalSource="NIKU"
            objectType="customObjectInstance" 
    
    
    overrideAutoNumbering ="false" version="12.0.6.5471"/>
        <customObjectInstances objectCode="axa_nrr">
            <instance instanceCode="-1" objectCode="axa_nrr"
                parentInstanceCode="82056" parentObjectCode="project">
                <CustomInformation>
                    <ColumnValue name="actual_quantity">100</ColumnValue>
                    <ColumnValue name="actuals_date">2010-11-22T00:00:00</ColumnValue>
                    <ColumnValue name="code">""</ColumnValue>
                    <ColumnValue name="odf_cncrt_parent_id">5022937</ColumnValue>
                    <ColumnValue name="odf_parent_id">5022937</ColumnValue>
                    <ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
                    <ColumnValue name="task_id">5172986</ColumnValue>
                    [color=#FF0825]<ColumnValue name="resource_id">5008860</ColumnValue>[/color]               
    <ColumnValue name="status">1</ColumnValue>
                </CustomInformation>
                <OBSAssocs completed="false"/>
                        </instance>
        </customObjectInstances>
        <XOGOutput>
            <Object type="customObjectInstance"/>
            <Status state="SUCCESS"/>
            <Statistics failureRecords="0" insertedRecords="0"
                totalNumberOfRecords="1" updatedRecords="0"/>
            <Records/>
        </XOGOutput>
    </NikuDataBus>
    My problem is that this will error out with the following message if I leave the Resource_ID entry in
    ErrorInformation>
                    <Severity>FATAL</Severity>
                    <Description>com.niku.union.odf.exception.ODFValidationException: Validation failure. Attribute: resource_id. Cause: lookup_value</Description>
                </ErrorInformation>
    If I take the Resource_ID line out it works, but obviously the corresponding resource attribute is null and for what I'm trying to achieve I can't have a null there.

    In the GUI the Resource ID is derived from a parameterised lookup which evaluates to SRM_RESOURCES.ID: the code for that is:
    SELECT
    @SELECT:RES.ID:RESOURCE_ID@,
    @SELECT:RES.FULL_NAME:FULL_NAME@,
    @SELECT:PAC.TRANSCLASS:TRANSCLASS@
                                    
    FROM 
    SRM_RESOURCES RES, 
    PAC_MNT_RESOURCES PAC,
    PRTEAM T               
    
    WHERE
    RES.IS_ACTIVE = 1
    AND T.PRRESOURCEID = RES.ID
    AND PAC.ID = RES.ID
    AND (PAC.RESOURCE_CLASS = 'non lab' or PAC.TRANSCLASS in ('***', 'YYY'))
    AND @where:param:USER_DEF:integer:project_int_id@ = T.PRPROJECTID
    AND @WHERE:SECURITY:RESOURCE:RES.ID@                       
    
    @BROWSE-ONLY:
    and (@where:param:USER_DEF:integer:project_int_id@ is null 
    or @where:param:USER_DEF:integer:project_int_id@ = T.PRPROJECTID)
    :BROWSE-ONLY@
    and @FILTER@
    The hard coded resource ID in the XML above does exist in the database, before anyone asks :wink:
    Task_ID in the same object also gets its value from a parameterised lookup so I'm a bit confused. Is there anything particular I need to bear in mind with lookups and XOGging custom attributes?

    Thanks

    Owen


  • 2.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 24, 2010 08:44 AM
    What happens when you create a sub-object instance in the GUI then XOG it "Out" - do you get the resource ID (or some other value) in the XOG file?

    (It all sounds a bit odd to me!)


  • 3.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 24, 2010 08:50 AM
    Yup - resource id comes out in the 5nnnnnn format as I would expect.


  • 4.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 24, 2010 09:13 AM
    ....and the [font=Courier New][color=#F30808]5008860[color][font] value is 'valid' based on the value of the parameter in your parametrised lookup?

    (I assume your parametrised lookup is linked correctly to the project id in the subobject... I mean that the lookup will return that Resource ID ok for that project id as a parameter?)

    I think I'm just stating the "obvious" questions here though. :wacko::


  • 5.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 24, 2010 10:00 AM
    Yes to both - the lookups are fine in the GUI and when I create an instance there then query the ODF_CA table, all the data values are present and correct in the right format.

    I feel another support case coming on...


  • 6.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 24, 2010 10:10 AM
    I am just throwing this out there...
    Is there any reason that the look-up would be choking on a security issue associated with the xog user?
    I am just thinking about lookups that incorporate the security line, so if a resource doesn't have view rights to the resource, the lookup filters them out.
    Just a random thought...
    Andrea


  • 7.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 24, 2010 10:19 AM
    ^ I like that thought!

    That security line in the lookup definition
    AND @WHERE:SECURITY:RESOURCE:RES.ID@
    could be the culprit - assuming that the XOG is being performed by a different (admin?) user than is used when you run thru the GUI?


  • 8.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 24, 2010 10:30 AM
    I'm going to disappoint you now..,

    So I logged on as admin and ran the XML (was previously logged on as my own ID which has all the XOG rights anyway, but just to check)...and it failed - same error.

    Repeated the process having taken the security clause out of that lookup - failed, same error :what:


  • 9.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 24, 2010 11:04 AM
    In the meantime I've been building my GEL script around this. My initial SQL statement that I'm using to get my data is giving me the following attribute values in the XML when I run it
    <sql:query var="nrr" dataSource="${clarityDS}">
    
    
    select
    
    
    srm.unique_name as project_code,
    
    
    nrr.odf_cncrt_parent_id,
    
    
    nrr.odf_parent_id,
    
    
    getdate() as actuals_date,
    
    
    nrr.task_id
    
    
    
    
    
    from
    
    
    odf_ca_axa_nrr nrr, 
    
    
    ppa_transcontrol tc,
    
    
    srm_projects srm
    
    
    
    
    
    where
    
    
    nrr.code = tc.external_id
    
    
    and nrr.odf_parent_id = srm.id
    
    
    and nrr.status = 3
    
    
    </sql:query>
    <!-- Snip-->
                    <CustomInformation>
                    <ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
                    <ColumnValue name="odf_cncrt_parent_id">"5022937"</ColumnValue>
                    <ColumnValue name="odf_parent_id">"5022937"</ColumnValue>
                    <ColumnValue name="code">""</ColumnValue>
                    <ColumnValue name="actuals_date">"2010-11-24 15:44:04.813"</ColumnValue>
                    <ColumnValue name="actual_quantity">0</ColumnValue>
                    <ColumnValue name="task_id">"5172987"</ColumnValue>
                    <ColumnValue name="status">1</ColumnValue>
                    <ColumnValue name="notes">Automatically created</ColumnValue>
                </CustomInformation>
    Writing this to Clarity via a process/GEL also fails with the same error, this time on Task ID but if you put the equivalent data in the GUI XOG it will run ok - very strange.

    But what I was wondering is whether the quotes on Task ID are significant - am I trying to write a string when it needs a number (the database column for Task ID is numeric(38,0) )? Status and Actual Quantity are hard coded by me as 1 and 0 and don't come from the query.

    <Edit> Actually, putting the task id in quotes in the GUI XOG will also cause it to fail, so I think it could be a type mismatch problem?


  • 10.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 24, 2010 11:27 AM
    Yeah, I can understand the quotes causing type-mismatch problems - but your original XML right at the top of the thread doesn't have any quotes in it... so the quotes are only causing this "second problem"; when you are running the XOG from within GEL.

    I would say that the "proof" that there is a CA-BUG here is just if you XOG out an instance that you created in the application and then fail to be able to XOG it back in again (running the XOG as the same user that you used in the application). Then you have a fairly simple test case which you could report a bug against.

    But if the test case "works"; then YOU are doing something wrong rather than Clarity! :glare:


  • 11.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 24, 2010 12:56 PM
    What version are you running?
    I was having xog weirdness on 12.0.6. and I had to run the hotfix to clear it up.
    Now it works like a charm...


  • 12.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 24, 2010 04:00 PM
    Hi Andrea
    I've read your posts on that subject with interest! We're on 12.0.6 but without the hotfix, and no imminent chance of that going on for now :sad

    Will do some more testing of this and keep you posted.


  • 13.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 25, 2010 10:11 AM
    I would highly recommend the hotfix (we used Hotfix 11...not the 12 that seems to be causing more problems than it solves...)
    It literally took 3 minutes to run and fixed all my xog problems. (Though sadly, not my problems with Financials...)
    Good luck!
    Keep us posted!
    Andrea


  • 14.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 25, 2010 10:24 AM

    Dave wrote:

    I would say that the "proof" that there is a CA-BUG here is just if you XOG out an instance that you created in the application and then fail to be able to XOG it back in again (running the XOG as the same user that you used in the application). Then you have a fairly simple test case which you could report a bug against.
    So I've done this again today just to prove I'm not going mad and I noticed an interesting thing. My lookup for the Task ID was storing PRTASK.PRID and returning PRUID, PREXTERNALID and PRNAME in that order. When I read the object instance out, XOG was returning PRUID and not PRID as I would expect. So that seems like bug # 1. Taking PRUID out of the lookup definition results in PRTASK.PRID coming back in the read XML.

    If I then write that XML back to Clarity, completely as is, having first deleted the corresponding instance from the GUI, then if Resource ID is in the write file it will fail with the error described above. That's # 2.

    To get round #2, I have created another attribute Resource ID 2 which is a straight number field, with no lookups. I have an Update DB trigger to populate that with the value from Resource ID. My GEL script will read that value and write it back into the same column in the new instances, and then I have an Insert trigger to update Resource ID from the inserted Resource ID 2 value. Not a very elegant solution but it seems to be working.

    I'll repost any findings from Support once I've logged the case.


  • 15.  RE: XOG in Custom Object Instance - Lookup Error
    Best Answer

    Posted Nov 25, 2010 05:52 PM
    Sad to say I've just found that the lookup issue is a known bug - (CLRT-20830)

    TEC524300 - Clarity: XOG write of custom instances fail if it includes an attribute based on a number lookup

    Thanks for your input Dave and Andrea.

    Owen


  • 16.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Nov 26, 2010 10:21 AM
    Wow. Do they not have a hotfix to address this?
    I would think that would be a rather radical issue for some customers...


  • 17.  RE: XOG in Custom Object Instance - Lookup Error

    Posted Aug 08, 2012 02:03 PM
    Any clue on whether this issue (TEC524300 - Clarity: XOG write of custom instances fail if it includes an attribute based on a number lookup) has been resolved?

    Thanks
    BB