Parameterized lookup value is updated incorrectly via XOG when lookup query is missing @[email protected]

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

A parameterized lookup value is xogging in incorrectly. It always defaults to one value irrespective of the value in the XML for the XOG.

Steps to Reproduce:

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 a Dynamic Query lookup: Lookup_PARA
3. Open the Benefit Plan Detail object
4. Create 3 attributes:
   • MYFUNCTION. Associate it to LookupStc_FUNCTION
   • MYSUBFUNC. Associate it toLookupStc_SUBFUNCTION
   • MYPARA. Associated it to Lookup_PARA  (Parameterized)
   • Map the Lookup Parameter with Object Attribute ID=MYFUNCTION
5. Add the attributes to the views
6. Open a Project and Create a Benefit plan.
7. In the Benefit Plan Detail enter:
   •  MYFUNCTION  = aaa (this is value ID 111)
   • MYPARA= xxx (this is value ID 111002)
8. 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>
   
   
9. Modify the file with different values and use it as the input file:
   • bbb (this is value ID 222)
   • yyy (this is value ID 222001)
10. XOG the file into Clarity
    Results: Output file successful: 1 record updated
11. 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: Parameterized lookup value should be updated with the value specified in the XOG input file.

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

This issue is seen both in Classic PPM and Clarity (Modern User Experience (UX))

Cause

This is working as designed. This is due to the where clause missing @[email protected], so the query is not able to apply the filter on the lookup value provided. Note: Te Query tab in Administration --> Data Administration --> Lookups mentions this:
"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 )

Example of a query missing this clause:

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] = '')

Environment

Release: All

Resolution

Add AND @[email protected] in the NSQL WHERE clause of your dynamic lookup definition