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

Document ID : KB000003953
Last Modified Date : 10/08/2018
Show Technical Document Details
Issue:

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 "@FILTER@" 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 )

Resolution:

This is not a bug.

This is the NSQL you are using:
SELECT
  @SELECT:lkp.lookup_enum:lookup_enum@
, @SELECT:nls.name:name@
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:LANGUAGE@
      AND (@WHERE:PARAM:USER_DEF:INTEGER:csc_subfunc_id@ = LPAD(lkp.lookup_code,3) OR
          @WHERE:PARAM:USER_DEF:INTEGER:csc_subfunc_id@ IS NULL OR
          @WHERE:PARAM:USER_DEF:INTEGER:csc_subfunc_id@ = '')


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