CREATE OR REPLACE FUNCTION WB_RT_GET_PARENT_AUDIT_ID
(
  audit_id NUMBER
) 
RETURN NUMBER AS 
  result NUMBER; 
BEGIN 
begin
  if (audit_id is null) then 
    result := null;
  else 
    select parent_execution_audit_id 
    into result
    from all_rt_audit_executions 
    where execution_audit_id = audit_id;
   end if;
   
  EXCEPTION 
    WHEN OTHERS THEN 
    return null;       
end;    
  
  return result;
END;
/

CREATE OR REPLACE PROCEDURE WB_RT_GET_JOB_METRICS
(
  audit_id NUMBER, 
  nselected out NUMBER, 
  ndeleted out NUMBER, 
  nupdated out NUMBER, 
  ninserted out NUMBER, 
  ndiscarded out NUMBER, 
  nmerged out NUMBER, 
  ncorrected out NUMBER
) 
AS 
BEGIN 

  if (audit_id is not null) then 
    select
      NUMBER_RECORDS_SELECTED,
      NUMBER_RECORDS_DELETED,    
      NUMBER_RECORDS_UPDATED,
      NUMBER_RECORDS_INSERTED,
      NUMBER_RECORDS_DISCARDED,
      NUMBER_RECORDS_MERGED,
      NUMBER_RECORDS_CORRECTED
    into
      nselected, 
      ndeleted, 
      nupdated, 
      ninserted, 
      ndiscarded, 
      nmerged, 
      ncorrected  
    from
      all_rt_audit_map_runs
    where
      EXECUTION_AUDIT_ID = audit_id;      
  end if;    
    
  EXCEPTION 
    WHEN OTHERS THEN 
    null;        
END;
/

CREATE OR REPLACE FUNCTION WB_RT_GET_START_TIME
(
  audit_id NUMBER
) 
RETURN TIMESTAMP WITH TIME ZONE AS 
  l_date DATE; 
BEGIN 
begin
  if (audit_id is null) then 
    l_date := null;
  else 
    select created_on 
    into l_date
    from all_rt_audit_executions 
    where execution_audit_id = audit_id;
   end if;
   
  EXCEPTION 
    WHEN OTHERS THEN 
    return null;       
end;    
  
  return to_timestamp_tz(to_char(l_date, 'YYYY-MM-DD HH24:MI:SS'),
              'YYYY-MM-DD HH24:MI:SS:FF9 TZH:TZM');
END;
/

CREATE OR REPLACE FUNCTION WB_RT_GET_ELAPSED_TIME
(
  audit_id NUMBER
) 
RETURN NUMBER AS 
  result NUMBER; 
BEGIN 
begin
  if (audit_id is null) then 
    result := null;
  else 
    select elapse_time 
    into result
    from all_rt_audit_executions 
    where execution_audit_id = audit_id;
   end if;
   
  EXCEPTION 
    WHEN OTHERS THEN 
    return null;       
end;  
  return result;
END;
/

CREATE OR REPLACE FUNCTION WB_RT_GET_NUMBER_OF_ERRORS
(
  audit_id NUMBER
) 
RETURN NUMBER AS 
  result NUMBER; 
BEGIN 
begin
  if (audit_id is null) then 
    result := null;
  else 
    select number_task_errors 
    into result
    from all_rt_audit_executions 
    where execution_audit_id = audit_id;
   end if;
   
  EXCEPTION 
    WHEN OTHERS THEN 
    return null;       
 end;   
  
  return result;
END;
/

CREATE OR REPLACE FUNCTION WB_RT_GET_NUMBER_OF_WARNINGS
(
  audit_id NUMBER
) 
RETURN NUMBER AS 
  result NUMBER; 
BEGIN 
begin
  if (audit_id is null) then 
    result := null;
  else 
    select number_task_warnings 
    into result
    from all_rt_audit_executions 
    where execution_audit_id = audit_id;
   end if;
   
  EXCEPTION 
    WHEN OTHERS THEN 
    return null;       
end;  
  return result;
END;
/

CREATE OR REPLACE FUNCTION WB_RT_GET_RETURN_CODE
(
  audit_id NUMBER
) 
RETURN NUMBER AS 
  result NUMBER; 
BEGIN 
begin
  if (audit_id is null) then 
    result := null;
  else 
    select return_code 
    into result
    from all_rt_audit_executions 
    where execution_audit_id = audit_id;
   end if;
   
  EXCEPTION 
    WHEN OTHERS THEN 
    return null;       
end;  
  return result;
END;
/

CREATE OR REPLACE FUNCTION WB_RT_GET_MAP_RUN_AUDIT_ID
(
  audit_id NUMBER
) 
RETURN NUMBER AS 
  result NUMBER; 
BEGIN 
begin
  if (audit_id is null) then 
    result := null;
  else 
    select map_run_id 
    into result
    from all_rt_audit_map_runs
    where execution_audit_id = audit_id;
   end if;

  EXCEPTION 
    WHEN OTHERS THEN 
    return null;      
end;  
  return result;
END;
/

CREATE OR REPLACE FUNCTION WB_RT_GET_LAST_EXECUTION_TIME
(
  objectName VARCHAR2, 
  objectType VARCHAR2,  
  objectLocationName VARCHAR2
) 
RETURN DATE 
AS 
  l_date DATE;
BEGIN 
BEGIN 
  select 
    max(CREATED_ON) as START_TIME 
  into
    l_date
  from 
    all_rt_audit_executions 
  where 
    EXECUTION_AUDIT_STATUS = 'COMPLETE' 
  and 
    object_name= objectName 
  and 
    OBJECT_LOCATION_NAME = objectLocationName     
  AND 
  (   
    OBJECT_TYPE = decode(objectType, 'MAPPING', 'PLSQLMap')   
  OR 
    OBJECT_TYPE = decode(objectType, 'MAPPING', 'DataProfile')   
  OR 
    OBJECT_TYPE = decode(objectType, 'MAPPING', 'DataAuditor') 
  OR 
    OBJECT_TYPE = decode(objectType, 'MAPPING', 'SQLLoaderControlFile') 
  OR 
    OBJECT_TYPE = decode(objectType, 'MAPPING', 'ABAPFile') 
  OR   
    OBJECT_TYPE = decode(objectType, 'DATA_AUDITOR', 'DataAuditor') 
  OR   
    OBJECT_TYPE = decode(objectType, 'PROCESS_FLOW', 'ProcessFlow') 
  OR   
    OBJECT_TYPE = decode(objectType, 'SCHEDULABLE', 'ScheduledJob')     
  OR   
    OBJECT_TYPE = decode(objectType, 'REAL_TIME_MAPPING', 'PLSQLMap') 
  );     
       
  EXCEPTION 
    WHEN OTHERS THEN 
    return null; 
END;    
return l_date;    
END;
/