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

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

book

Article ID: 130650

calendar_today

Updated On:

Products

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 a Static lookup:
    1. Go to Administration => Data Administration => Lookups
    2. Click New -  and select Static List
      1. Enter name: BC - Agregador (Z_LCKP_AGREGADOR)
      2. Add some values:
        • Structure
        • Department
        • Other
  2. Create another Static lookup:
    1. Go to Administration => Data Administration => Lookups
    2. Click New and select Static List
    3. Enter Name: BC - Programa (Z_LCKP_PROGRAMA)
    4. Add some values:
      • WAN
      • LAN
      • Fixed Voice
  3. Create a Custom Object
    1. Go to Administration => Studio => Objects
    2. Name of the object: Initiatives Mapping with ID: z_map_iniciativas
  4. Add two attributes to the object:
    • Agregador - Lookup-String - z_agregador (Lookup: BC - Agregador)
    • Programa - Lookup-String - z_programa (Lookup: BC - Programa)
  5. Add the two attributes (Agregador and Programa) to the Edit and List views of the object
  6. Add some instances of the Initiatives Mapping object
    Go to Home => Custom Objects => Initiatives Mapping List
  7. Create 3 instances under Initiatives Mapping List
    • Name: Test, Agreagador: Structure, Programa: WAN
    • Name: Test2, Agreagador: Department, Programa: LAN
    • Name: Test3, Agreagador: Other, Programa: WAN
  8. Create a Dynamic Lookup
    1. Go to Administration => Data Administration => Lookups
    2. Click New and select Dynamic Query
    3. Enter a name: BC - Iniciativas Programas Parameterized Lookup (Enter an ID: Z_LCKP_PROG_PARAM)
  9. 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]

  10. Create two new attributes on the Investment object
    1. Go to Administration => Studio => Objects
    2. Select the Investment object
    3. Go to attributes
    4. Create the two new attributes:
      • Aggregator - Lookup-String - z_agregador (Lookup: BC - Agregador)
      • Program - Lookup-String - z_programa (Lookup: BC - Iniciativas Programas Parameterized Lookup)
  11. On the Lookup Parameter Mappings for the Program Attribute, enter these values:
    • Lookup Parameter: type_constrain
    • Object Attribute ID: z_agregador
  12. Add the two attributes (Aggregator and Program) to the Edit views on the Idea object.
    1. Go to Administration -> Studio -> Objects
    2. Select the Idea object.
    3. Go to Views
    4. Go to the edit layout: (Partition: System | View: Idea Properties | Mode: Edit - Property Layout)
    5. Add the two attributes (Aggregator and Program) to the Selected list on the General Section
  13. Add the two attributes (Aggregator and Program) to the list of audited attributes on the Idea object for the Insert and Update Operations
  14. Edit one of the Idea instances:
    1. Go to Home => Demand Management => Ideas
    2. Click on the Idea
    3. Enter a value for the attribute Aggregator: (i.e: Department)
    4. Enter a value for the attribute Program: (LAN)
    5. Click Save
  15. 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

Environment

All Supported Releases

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 a 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

Resolution

Update the query so the scalar function is outside the filter so that it gets ignored in scalar function creation. 

Example:

  1. In the above problematic query, copy and cut the following line:

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

  2. 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]

Powered by Wolken Software