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

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM SaaS - Application
• Clarity PPM On Premise

Issue/Introduction

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:

LookupStc_FUNCTION

Value     ID
----------------
 aaa     111
 bbb     222

LookupStc_SUBFUNCTION

Value     ID
----------------
 www   111001
 xxx     111002
 yyy     222001
 zzz     222002

2. Create Dynamic Query lookup:
Lookup_PARA

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:

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

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

Workaround:  

Cause

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 )

Environment

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

Resolution

This is not a bug.

This is the NSQL you are using:
SELECT
  @SELECT:lkp.lookup_enum:[email protected]
, @SELECT:nls.name:[email protected]
FROM cmn_lookups lkp
INNER JOIN cmn_captions_nls nls ON nls.table_name = 'CMN_LOOKUPS' AND nls.pk_id = lkp.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.