FND Audit Table Changes by Column

Description
Categories: Audit, Enginatics, Toolkit - Data Management, Toolkit - Metrics, Toolkit - Operations, Toolkit - Setup & Support
Repository: Github Columns: Audit Table, Audit Timestamp, Audit Transaction Type, Audit Transaction User, Ledger Id, Audit Column, Old Value, New Value, Row Key, Audit Session Id ...
Reports all changes to an audited application table.

The report has one row per audit transaction and audited column showing the old and new audit column value.
select
q2.*
from
(
select
:audit_table audit_table,
xxen_util.client_time(q1.audit_timestamp) audit_timestamp,
xxen_util.meaning(q1.audit_transaction_type,'FND_AUDIT_TRANS_TYPE',0) audit_transaction_type,
xxen_util.user_name(q1.audit_user_name) audit_transaction_user,
&sel_xinfo_table_user_cols
&sel_base_table_user_cols
&sel_audit_key_cols
q1.audit_column audit_column,
case when q1.a_val is null and q1.true_null='N' then null else q1.a_val end old_value,
case q1.audit_transaction_type when 'D' then null when 'I' then q1.ac1_val else case when q1.a_val is null and q1.true_null='N' then null else q1.ac1_val end end new_value,
to_char(q1.row_key) row_key,
q1.audit_session_id,
q1.audit_sequence_id,
q1.audit_commit_id,
q1.column_sequence_id,
q1.audit_transaction_type audit_trx_type
from
(
select 
aud_a.*,
col.column_name audit_column,
col.sequence_id column_sequence_id,
nvl(substr(aud_a.audit_true_nulls,col.sequence_id+1,1),'N') true_null,
case col.column_name
&sel_audit_cols_old_vals
end a_val,
case col.column_name
&sel_audit_cols_new_vals
end ac1_val
from
&from_audit_tables
(
select distinct
fc.column_name,
fac.sequence_id
from
fnd_tables ft,
fnd_audit_columns fac,
fnd_columns fc
where
1=1 and
ft.table_name=:audit_table and
ft.application_id=fac.table_app_id and
ft.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.column_name
) col
where
2=2 and
&join_audit_tab_key_cols
aud_a.row_key=aud_ac1.row_key
) q1,
&from_tables
where
&join_base_table_key_cols
&join_xinfo_table_key_cols
3=3
) q2
where
4=4
order by
q2.audit_table,
q2.row_key desc,
q2.column_sequence_id
/*&dummy*/
Parameter Name SQL text Validation
Audited Users
aud_a.audit_user_name=:audited_user
LOV
Audit Table
 
LOV
Include Audit Columns
fc.column_name=:audit_column
LOV
Additional Info Table
 
LOV
Additional Info Table Columns
x=:dummy
LOV
Informational Audit Table Columns
x=:dummy
LOV
Audit Date From
aud_a.audit_timestamp>=:date_from
DateTime
Audit Date To
aud_a.audit_timestamp<=:date_to
DateTime
Record Filter
nvl(q2.old_value,'#ISNULL')!=nvl(q2.new_value,'#ISNULL') or q2.audit_trx_type='I'
LOV
Audit Table
select
lower(
substr(:audit_table,1,24)||'_a aud_a,'||chr(10)||
substr(:audit_table,1,24)||'_ac1 aud_ac1,'
) text
from dual
Audit Table
select lower(: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' else
'aud_a.'||fc.column_name||'=aud_ac1.'||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 fc.null_allowed_flag='Y' and fc.primary_key_sequence>1 then
'nvl(q1.'||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
'q1.'||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
x.columns
from
(
select distinct
'when '''||fc.column_name||''' then to_char(aud_ac1.'||lower(fc.column_name)||')' columns,
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 
) x
Audit Table
select
x.columns
from
(
select distinct
'when '''||fc.column_name||''' then to_char(aud_a.'||lower(fc.column_name)||')' columns,
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 
) x
Audit Table
select
x.column_name
from
(
select distinct 
'q1.'||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='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
order by 
fac.sequence_id
) x
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
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(q1.'||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
'q1.'||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
Record Filter
nvl(q2.old_value,'#ISNULL')!=nvl(q2.new_value,'#ISNULL')