Parameterized dynamic lookup values are blank in Audit Trail

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:PROJ_ID@ and @FILTER@
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