Parameterized lookup field is not properly audited - Blank for update and insert

book

Article ID: 130650

calendar_today

Updated On:

Products

CLARITY PPM FOR ITG CLARITY PPM FEDERAL Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

An attribute based on a parameterized lookup is not properly being audited.
The entry for the insert and/or update operations of the attribute is being created on the Audit Trail tables, but the values are blank for the OLD and NEW values of the attribute.

STEPS TO REPRODUCE

1. Create Static lookup:
Administration => Data Administration => Lookups
New - Static List
BC - Agregador (Z_LCKP_AGREGADOR)

Add some values
- Structure
- Department
- Other

2. Create Static lookup:
Administration => Data Administration => Lookups
New - Static List
BC - Programa (Z_LCKP_PROGRAMA)

Add some values
- WAN
- LAN
- Fixed Voice

3. Create a Custom Object
Administration => Studio => Objects
Initiatives Mapping - z_map_iniciativas

  • Add two attributes
    Agregador - Lookup-String - z_agregador (Lookup: BC - Agregador)
    Programa - Lookup-String - z_programa (Lookup: BC - Programa)
  • Add the two attributes (Agregador and Programa) to the Edit and List views of the object

4. Add some instances of the Initiatives Mapping object
Home => Custom Objects => Initiatives Mapping List

  • Create 3 instances
    Initiatives Mapping List

    Name: Test, Agreagador: Structure, Programa: WAN
    Name: Test2, Agreagador: Department, Programa: LAN
    Name: Test3, Agreagador: Other, Programa: WAN

5. Create Dynamic Lookup
Administration => Data Administration => Lookups
New - Dynamic Query
BC - Iniciativas Programas Parameterized Lookup (Z_LCKP_PROG_PARAM)

On the Niku Query enter the following code

SELECT DISTINCT
@SELECT:LOOKUP.LOOKUP_CODE:[email protected],
@SELECT:LOOKUP.NAME:[email protected],
@SELECT:LOOKUP.SORT_ORDER:[email protected],
@SELECT:LOOKUP.LAST_UPDATED_DATE:[email protected],
@SELECT:LANG.LANGUAGE_CODE:[email protected],
@SELECT:LANG.ID:[email protected]
FROM odf_ca_z_map_iniciativas MAP, CMN_LOOKUPS_V LOOKUP, CMN_LANGUAGES LANG
WHERE MAP.z_programa = LOOKUP.LOOKUP_CODE
AND LOOKUP.LANGUAGE_CODE = LANG.LANGUAGE_CODE
AND LOOKUP.LOOKUP_TYPE = 'Z_LCKP_PROGRAMA'
AND LOOKUP.LANGUAGE_CODE = @WHERE:PARAM:[email protected]
AND (@WHERE:PARAM:USER_DEF:STRING:[email protected] = MAP.z_agregador)
AND @[email protected]
@BROWSE-ONLY:
AND LOOKUP.IS_ACTIVE=1
:[email protected]


6. Create two new attributes on the Investment object
Administration => Studio => Objects
Select the Investment
Object: Investment - Attributes

  • Create the two new attributes:

    Aggregator - Lookup-String - z_agregador (Lookup: BC - Agregador)
    Program - Lookup-String - z_programa (Lookup: BC - Iniciativas Programas Parameterized Lookup)
  • On the Lookup Parameter Mappings for the Program Attribute enter those values
    Lookup Parameter: type_constrain
    Object Attribute ID: z_agregador

7. Add the two attributes (Aggregator and Program) to the Edit views on the Idea object.
Administration => Studio => Objects
Select the Idea object.
Go to Views
Object: Idea - Views
Object: Idea | Partition: System | View: Idea Properties | Mode: Edit - Property Layout
Add the two attributes (Aggregator and Program) to the Selected list on the General Section

8. Add the two attributes (Aggregator and Program) to the list of audited attributes on the Idea object.
Object: Idea - Audit Trail Attributes
Audited Attributes and Logged Attributes for Insert Operation 

9. Edit one of the Idea instances
Home => Demand Management => Ideas
Click on the Idea
Idea: - Properties - Main - General
Enter a value for the attribute Aggregator: (f.i Department)
Enter a value for the attribute Program: (LAN)

Click Save

10. Click on the Audit tab on the Idea instance modified.

Expected Results: Entries are created for both Aggregator and Program attributes on the Audit Trail.
The Old Value and New Value are logged on those entries

Actual Results: Entries are created for both Aggregator and Program attributes on the Audit Trail.
The Old Value and New Value are blank for the attribute Program

Cause

  • This issue is caused by the where condition (AND (@WHERE:PARAM:USER_DEF:STRING:[email protected] = MAP.z_agregador) 
    • This line is added to filter the program based on aggregation value, which is a scalar function. 
  • As scalar function will not have the context, the where clause gets converted as NULL = MAP.z_aggregation and fails to return audit data for the field

Environment

All Supported Releases

Clarity PPM Studio

Resolution

  1. Update the query so the scalar function is outside the filter so that it gets ignored in scalar function creation. 
  2. See example below:
  • In the above problematic query, copy and cut the following line:

(AND (@WHERE:PARAM:USER_DEF:STRING:[email protected] = MAP.z_agregador)

  • And paste / move below the following lines:

AND @[email protected]
@BROWSE-ONLY:

  • Corrected query:

SELECT DISTINCT
@SELECT:LOOKUP.LOOKUP_CODE:[email protected],
@SELECT:LOOKUP.NAME:[email protected],
@SELECT:LOOKUP.SORT_ORDER:[email protected],
@SELECT:LOOKUP.LAST_UPDATED_DATE:[email protected],
@SELECT:LANG.LANGUAGE_CODE:[email protected],
@SELECT:LANG.ID:[email protected]
FROM odf_ca_z_map_iniciativas MAP, CMN_LOOKUPS_V LOOKUP, CMN_LANGUAGES LANG
WHERE MAP.z_programa = LOOKUP.LOOKUP_CODE
AND LOOKUP.LANGUAGE_CODE = LANG.LANGUAGE_CODE
AND LOOKUP.LOOKUP_TYPE = 'Z_LCKP_PROGRAMA'
AND LOOKUP.LANGUAGE_CODE = @WHERE:PARAM:[email protected]
AND @[email protected]
@BROWSE-ONLY:
AND (@WHERE:PARAM:USER_DEF:STRING:[email protected] = MAP.z_agregador)
AND LOOKUP.IS_ACTIVE=1
:[email protected]

Additional Information

See KB 141061 - How to search for known Clarity PPM Issues using Self Service

Powered by Wolken Software