FND Audit Table Changes by Record

Description
Categories: Audit, Enginatics, Toolkit - Data Management, Toolkit - Metrics, Toolkit - Operations, Toolkit - Setup & Support
Repository: Github
Reports all changes to an audited application table.

The report has one row per audit transaction showing the old and new values for all audited columns in a single row.
Run FND Audit Table Changes by Record and other Oracle EBS reports with Blitz Report™ on our demo environment
select
:audit_table audit_table,
xxen_util.client_time(aud_a.audit_timestamp) audit_timestamp,
xxen_util.meaning(aud_ac1.audit_transaction_type,'FND_AUDIT_TRANS_TYPE',0)  audit_transaction_type,
xxen_util.user_name(aud_ac1.audit_user_name) audit_transaction_user,
&sel_xinfo_table_user_cols
&sel_table_user_cols
&sel_audit_key_cols
&sel_audit_cols
to_char(aud_a.row_key) audit_row_key,
aud_a.audit_session_id,
aud_a.audit_sequence_id,
aud_a.audit_commit_id
from
&from_tables
where
aud_a.row_key=aud_ac1.row_key and
&join_audit_tab_key_cols
&join_xinfo_table_key_cols
1=1
order by
aud_a.row_key desc
/*&dummy*/
Parameter Name SQL text Validation
Audit Table
 
LOV
Audit Date From
aud_a.audit_timestamp>=:date_from
DateTime
Audit Date To
aud_a.audit_timestamp<=:date_to
DateTime
Audited User
aud_a.audit_user_name=:audited_user
LOV
Informational Audit Table Columns
x=:dummy
LOV
Additional Info Table
 
LOV
Additional Info Table Columns
x=:dummy
LOV
Audit Table
select
lower(
substr(:audit_table,1,24)||'_a aud_a,'||chr(10)||
substr(:audit_table,1,24)||'_ac1 aud_ac1,'||chr(10)||
:audit_table||' tab'
) text
from dual
Audit Table
select
case when fc.null_allowed_flag='Y' and fc.primary_key_sequence>1 then
'nvl(aud_a.'||fc.column_name||','||decode(fc.column_type,'D','sysdate','N','-99','''x''')||')=nvl(aud_ac1.'||fc.column_name||','||decode(fc.column_type,'D','sysdate','N','-99','''x''')||') and'||chr(10)||
'nvl(aud_a.'||fc.column_name||','||decode(fc.column_type,'D','sysdate','N','-99','''x''')||')=nvl(tab.'||fc.column_name||'(+)'||','||decode(fc.column_type,'D','sysdate','N','-99','''x''')||') and' else
'aud_a.'||fc.column_name||'=aud_ac1.'||fc.column_name||' and'||chr(10)||
'aud_a.'||fc.column_name||'=tab.'||fc.column_name||'(+) and'
end text
from (
select distinct
lower(fc.column_name) column_name,
fac.sequence_id,
fc.column_type,
fc.null_allowed_flag,
fpkc.primary_key_sequence
from
fnd_tables ft,
fnd_audit_tables fat,
fnd_audit_columns fac,
fnd_columns fc,
fnd_primary_key_columns fpkc
where
ft.table_name=:audit_table and
ft.application_id=fat.table_app_id and
ft.table_id=fat.table_id and
fat.state in('E','G','N') and
fat.table_id=fac.table_id and
fat.table_app_id=fac.table_app_id and
fac.state='K' and
fac.sequence_id>=0 and
fac.table_app_id=fc.application_id and
fac.table_id=fc.table_id and
fac.column_id=fc.column_id and
fc.application_id=fpkc.application_id and
fc.table_id=fpkc.table_id and
fc.column_id=fpkc.column_id
order by
fac.sequence_id
) fc
Audit Table
select
'case when aud_a.'||fc.column_name||' is null and nvl(substr(aud_a.audit_true_nulls,'||to_char(fc.sequence_id+1)||',1),''N'')=''N'' then null else aud_a.'||fc.column_name||' end old_'||substr(fc.column_name,1,26)||',
case aud_a.audit_transaction_type when ''D'' then null when ''I'' then aud_ac1.'||fc.column_name||' else case when aud_a.'||fc.column_name||' is null and nvl(substr(aud_a.audit_true_nulls,'||to_char(fc.sequence_id+1)||',1),''N'')=''N'' then null else aud_ac1.'||fc.column_name||' end end new_'||substr(fc.column_name,1,26)||',' columns
from
(
select distinct 
lower(fc.column_name) column_name,
fac.sequence_id
from
fnd_tables ft,
fnd_audit_tables fat,
fnd_audit_columns fac,
fnd_columns fc
where
ft.table_name=:audit_table and
ft.application_id=fat.table_app_id and
ft.table_id=fat.table_id and
fat.state in('E','G','N') and
fat.table_app_id=fac.table_app_id and
fat.table_id=fac.table_id and
fac.state='N' and
fac.sequence_id>=0 and
fac.table_app_id=fc.application_id and
fac.table_id=fc.table_id and
fac.column_id=fc.column_id
order by fac.sequence_id 
) fc
Audit Table
select distinct
'tab.'||lower(fc.column_name)||','
from
xxen_report_parameters_tl xrpt,
xxen_report_run_param_values xrrpv,
fnd_tables ft,
fnd_columns fc
where
xrpt.parameter_name='Informational Audit Table Columns' and
xrpt.language='US' and
xrpt.parameter_id=xrrpv.parameter_id and
xrrpv.run_id=:run_id and
xrrpv.value is not null and
ft.table_name=:audit_table and
ft.application_id=fc.application_id and 
ft.table_id=fc.table_id and
not exists (select null from fnd_audit_columns fac where fc.application_id=fac.table_app_id and fc.table_id=fac.table_id and fc.column_id=fac.column_id and fac.state='K') and --exclude key columns. these are always displayed anyway
instr(':'||translate(replace(replace(xrrpv.value,'<multiple_values>',null),chr(10),':'),',;:|','::::')||':',':'||fc.column_name ||':')>0
order by 1
Additional Info Table
select ','||chr(10)||lower(:additional_info_table)||' xtab' from dual
Additional Info Table
select
z.text
from
(
select
min(y.primary_key_id) over () min_primary_key_id,
y.primary_key_id,
y.primary_key_sequence,
' '||chr(10)||lower(y.text) text
from
(
select
ft.table_name,
fpkc.primary_key_id,
fpkc.primary_key_sequence,
case when fc.null_allowed_flag='Y' and fpkc.primary_key_sequence>1 then
'nvl(aud_a.'||fc.column_name||','||decode(fc.column_type,'D','sysdate','N','-99','''x''')||')=nvl(xtab.'||fc.column_name||'(+)'||','||decode(fc.column_type,'D','sysdate','N','-99','''x''')||') and' else
'aud_a.'||fc.column_name||'=xtab.'||fc.column_name||'(+) and'
end text,
max(nvl2(x.column_name,null,'Y')) over (partition by fpkc.primary_key_id) column_missing
from
fnd_tables ft,
fnd_primary_key_columns fpkc,
fnd_columns fc,
(
select
fc0.column_name
from
fnd_tables ft0,
fnd_audit_columns fac,
fnd_columns fc0
where
ft0.table_name=:audit_table and
ft0.table_id=fac.table_id and
ft0.application_id=fac.table_app_id and
fac.schema_id=-1 and
fac.table_app_id=fc0.application_id and
fac.table_id=fc0.table_id and
fac.column_id=fc0.column_id
) x
where
ft.table_name=:additional_info_table and
ft.application_id=fpkc.application_id and
ft.table_id=fpkc.table_id and
fpkc.application_id=fc.application_id and
fpkc.table_id=fc.table_id and
fpkc.column_id=fc.column_id and
fc.column_name=x.column_name(+)
) y
where
y.column_missing is null
) z
where
z.primary_key_id=z.min_primary_key_id
order by
z.primary_key_sequence
Additional Info Table
select distinct
'xtab.'||lower(fc.column_name)||' "'||substr(:additional_info_table||'.'||fc.column_name,1,30)||'",'
from
xxen_report_parameters_tl xrpt,
xxen_report_run_param_values xrrpv,
fnd_tables ft,
fnd_columns fc
where
xrpt.parameter_name='Additional Info Table Columns' and
xrpt.language='US' and
xrpt.parameter_id=xrrpv.parameter_id and
xrrpv.run_id=:run_id and
xrrpv.value is not null and
ft.table_name=:additional_info_table and
ft.application_id=fc.application_id and 
ft.table_id=fc.table_id and
instr(':'||translate(replace(replace(xrrpv.value,'<multiple_values>',null),chr(10),':'),',;:|','::::')||':',':'||fc.column_name ||':')>0