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
- ID: related_proj
- Name: Related Project
- Type: Lookup
- Lookup: Investment browse (INV_BROWSE_INVESTMENT)
- ID: related_task
- Name: Related Task
- Type: Lookup
- Lookup: related_task
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
Release : Any
Component : CA PPM STUDIO
DE56874, WNF
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.