ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Parameterized dynamic lookup values are blank in Audit Trail

book

Article ID: 196670

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

In the audit trail new entries are created for an attribute with modified dates but values are blank

STEPS TO REPRODUCE:

  1. Create a new lookup
    • Name: Related Task
    • ID: related_task
    • Source: Dynamic
  2. Use the following SQL: select prid, prname from prtask where prprojectid = @WHERE:PARAM:USER_DEF:INTEGER:[email protected] and @[email protected]
  3. Go to an object (can be ootb or custom) i.e. Project object
  4. Create new attribute
    • ID: related_proj
    • Name: Related Project
    • Type: Lookup
    • Lookup: Investment browse (INV_BROWSE_INVESTMENT)
  5. Create new attribute
    • ID: related_task
    • Name: Related Task
    • Type: Lookup
    • Lookup: related_task
  6. Click Save.
  7. Set proj_id lookup parameter mapping to related_proj
  8. Click Save again
  9. Place both attributes on an edit view
  10. Now go to Object - Audit
  11. Add Related Task for auditing on insert, update and delete
  12. Now open any Project and modify the value for Related Project and then pick a Related Task
  13. Go to Audit Trail to see the logged values

Expected Results: The change of values should be audited correctly.

Actual Results: New entries are created for this attribute with modified dates but values are Blank

Environment

Release : Any

Component : CLARITY STUDIO

Resolution

Due to the impact, Engineering has decided no changes will be done for this area of the product.

Workaround approved by Engineering:

When the audit is enabled for a field, a Function e gets generated at the Database (DB) level to fetch the old and new values for audit. In this particular case, the function is not generating correctly. 

  1. Below is the function definition which is auto-generated (example for Oracle):

    CREATE OR REPLACE FUNCTION clarity.odf_aud_5048360_fct(p_value text)
     RETURNS text
     LANGUAGE plpgsql
     STABLE SECURITY DEFINER
    AS $function$
    DECLARE
        V_RETURN_VALUE TEXT;
    BEGIN
       select prname into V_RETURN_VALUE 
     from (select prid, prname from prtask where prprojectid = NULL /* proj_id */ and 1=1) l where prid::text = P_VALUE;
    RETURN V_RETURN_VALUE;
    END; $function$
    ;
  2. This should be corrected as below 

    CREATE OR REPLACE FUNCTION clarity.odf_aud_5048360_fct(p_value text)
     RETURNS text
     LANGUAGE plpgsql
     STABLE SECURITY DEFINER
    AS $function$
    DECLARE
        V_RETURN_VALUE TEXT;
    BEGIN
       select prname into V_RETURN_VALUE 
     from (select prid, prname from prtask where 1=1) l where prid::text = P_VALUE;
    RETURN V_RETURN_VALUE;
    END; $function$
    ;

Note that the name of the function and syntax will be different in each environment based on the database vendor, attribute name, object enabled etc. You have to find out which function it is exactly by checking the Audit Trigger for the attribute code. If you need assistance with this, please contact Broadcom Support. 

Additional Information

See also: Lookups master KB for Clarity