CA PPM: Parameterised lookup value is updated incorrectly via XOG.


Article ID: 3953


Updated On:


Clarity PPM SaaS Clarity PPM On Premise


Parameterised lookup value is not xogging in correctly via XOG. Always defaults to one value irrespective of the value in xml xog.

Steps to Recreate:

1. Create 2 static lookups:


Value     ID
 aaa     111
 bbb     222


Value     ID
 www   111001
 xxx     111002
 yyy     222001
 zzz     222002

2. Create Dynamic Query lookup:

3. Open Object Name = Benefit Plan Detail

Create 3 attributes:

- MYFUNCTION. It uses Lookup = LookupStc_FUNCTION

- MYSUBFUNC. It uses Lookup = LookupStc_SUBFUNCTION

- MYPARA. It uses Lookup = Lookup_PARA  (Parameterized)
Map the Lookup Parameter with Object Attribute ID=MYFUNCTION

Add the attributes to the views

4. Open a Project and Create a Benefit plan. In the Benefit Plan Detail enter:
  MYFUNCTION  = aaa (this is value ID 111)
  MYPARA= xxx (this is value ID 111002)

5. XOG out the Benefit plan. Output file shows:

        <Detail detailName="Detal1">
            <ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
            <ColumnValue name="myfunction">111</ColumnValue>
            <ColumnValue name="mypara">111002</ColumnValue>

6. Modify file with different values and use it as input file:

bbb (this is value ID 222)
yyy (this is value ID 222001)

7. XOG it in. Output file successful: 1 record updated

8. Open the project and check Benefit Plan Detail:

  MYFUNCTION  = bbb (this is value ID 222) -> correct
  MYPARA= www (this is value ID 111001) -> Incorrect. This is not the value specified in the Xog input file. 

Expected Result:
Parameterised lookup value should be updated with the value specified in the XOG input file.

Actual Result:
Parameterised lookup value is not updated with the value specified in the XOG input file.

Keywords: Parameter, lookup, XOG



NOTE: the Query tab in Administration --> Data Administration --> Lookups clearly mentions:
"The WHERE clause must contain "@[email protected]" to work properly. To create a dependent lookup, enclose the lookup internal name in "@" symbols."

The problem is in the lookup definition ( dynamic NSQL in SubFunction_Parameterised(DYnamic).xml )


Release: 451-101-15.2-Clarity-Creator User License


This is not a bug.

This is the NSQL you are using:
  @SELECT:lkp.lookup_enum:[email protected]
,[email protected]
FROM cmn_lookups lkp
INNER JOIN cmn_captions_nls nls ON nls.table_name = 'CMN_LOOKUPS' AND nls.pk_id =
WHERE lookup_type = 'CSC_SUB_FUNC_STATIC' AND lkp.is_active = 1
      AND nls.language_code = @WHERE:PARAM:[email protected]
      AND (@WHERE:PARAM:USER_DEF:INTEGER:[email protected] = LPAD(lkp.lookup_code,3) OR
          @WHERE:PARAM:USER_DEF:INTEGER:[email protected] IS NULL OR
          @WHERE:PARAM:USER_DEF:INTEGER:[email protected] = '')

It does not have the @[email protected] in where clause. So it's not able to apply the filter on the lookup value provided.
If you just add the AND @[email protected] in the NSQL WHERE clause of your dynamic lookup definition, it works fine.