Parameterized dynamic lookup values are blank in Audit Trail (DE56874)

Products

• Clarity PPM On Premise
• Clarity PPM SaaS
• STARTER PACK-CLARITY PPM

Issue/Introduction

DE56874  Parameterized dynamic lookup values are blank in Audit Trail

STEPS TO REPRODUCE:

 1. Create 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 a 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 Resullts: The change of values should be audited correctly.

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

Workaround: none

Environment

Release : Any

Component : CA PPM STUDIO

Resolution

Due to the are impacted, Engineering has decided no changes will be done on the product area.

Workaround approved with Engineering:

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

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$
;

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$
;
 

Please note that 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.